I am working on writing a search method where a user can type a string of words and I return all the objects that have fields that match all of the words in one or a combination of fields. Person first_name last_name Pet name Person has_many :pets I want to write some SQL so that if I search for "Tony AAAA" I will get all the people who have Tony and AAA either in their first_name or last_name fields or in any of their pet''s name fields. For example, it would be ok to return a person if their first_name is Tony and they have a pet named AAAA. It would also be ok to return a person if their first name was Tony and last name was AAAA. I need for this to work for an arbitrary number of words in the search string so I assume that I will have to do some ruby string manipulation to modify the query. Any advice on how to start? I don''t know much SQL right now but I think a join would be used here as I need info from both the people table and pets table? Thank you, Matthew Margolis blog.mattmargolis.net
I can''t write the code for you because I''m learning and new myself. Forget joins if you set the relationships up in the models. Very easy though , you''ll use the params method in your form, then you''ll want to know everything about find() and it''s "conditions" parameter. At least that''s the place to start. Stuart On 6/19/06, Matthew Margolis <mrmargolis@wisc.edu> wrote:> I am working on writing a search method where a user can type a string > of words and I return all the objects that have fields that match all of > the words in one or a combination of fields. > > Person > first_name > last_name > > Pet > name > > Person has_many :pets > > I want to write some SQL so that if I search for "Tony AAAA" I will get > all the people who have Tony and AAA either in their first_name or > last_name fields or in any of their pet''s name fields. For example, it > would be ok to return a person if their first_name is Tony and they have > a pet named AAAA. It would also be ok to return a person if their first > name was Tony and last name was AAAA. > > I need for this to work for an arbitrary number of words in the search > string so I assume that I will have to do some ruby string manipulation > to modify the query. > Any advice on how to start? I don''t know much SQL right now but I think > a join would be used here as I need info from both the people table and > pets table? > > Thank you, > Matthew Margolis > blog.mattmargolis.net > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Dark Ambient wrote:> I can''t write the code for you because I''m learning and new myself. > Forget joins if you set the relationships up in the models. > Very easy though , you''ll use the params method in your form, then > you''ll want to know everything about find() and it''s "conditions" > parameter. > At least that''s the place to start. > > Stuart > > On 6/19/06, Matthew Margolis <mrmargolis@wisc.edu> wrote: >> I am working on writing a search method where a user can type a string >> of words and I return all the objects that have fields that match all of >> the words in one or a combination of fields. >> >> Person >> first_name >> last_name >> >> Pet >> name >> >> Person has_many :pets >> >> I want to write some SQL so that if I search for "Tony AAAA" I will get >> all the people who have Tony and AAA either in their first_name or >> last_name fields or in any of their pet''s name fields. For example, it >> would be ok to return a person if their first_name is Tony and they have >> a pet named AAAA. It would also be ok to return a person if their first >> name was Tony and last name was AAAA. >> >> I need for this to work for an arbitrary number of words in the search >> string so I assume that I will have to do some ruby string manipulation >> to modify the query. >> Any advice on how to start? I don''t know much SQL right now but I think >> a join would be used here as I need info from both the people table and >> pets table? >> >> Thank you, >> Matthew Margolis >> blog.mattmargolis.net >> >> _______________________________________________ >> Rails mailing list >> Rails@lists.rubyonrails.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/railsStuart, Thank you for the reply. I have been working in rails for around a year so I am familiar with find. I was hoping for some info about SQL that I could put in :conditions or just SQL for use in a find_by_sql to achieve the effect that is described above. Any advice anyone? Matthew Margolis blog.mattmargolis.net
On Mon, 2006-06-19 at 15:07 -0500, Matthew Margolis wrote:> Dark Ambient wrote: > > I can''t write the code for you because I''m learning and new myself. > > Forget joins if you set the relationships up in the models. > > Very easy though , you''ll use the params method in your form, then > > you''ll want to know everything about find() and it''s "conditions" > > parameter. > > At least that''s the place to start. > > > > Stuart > > > > On 6/19/06, Matthew Margolis <mrmargolis@wisc.edu> wrote: > >> I am working on writing a search method where a user can type a string > >> of words and I return all the objects that have fields that match all of > >> the words in one or a combination of fields. > >> > >> Person > >> first_name > >> last_name > >> > >> Pet > >> name > >> > >> Person has_many :pets > >> > >> I want to write some SQL so that if I search for "Tony AAAA" I will get > >> all the people who have Tony and AAA either in their first_name or > >> last_name fields or in any of their pet''s name fields. For example, it > >> would be ok to return a person if their first_name is Tony and they have > >> a pet named AAAA. It would also be ok to return a person if their first > >> name was Tony and last name was AAAA. > >> > >> I need for this to work for an arbitrary number of words in the search > >> string so I assume that I will have to do some ruby string manipulation > >> to modify the query. > >> Any advice on how to start? I don''t know much SQL right now but I think > >> a join would be used here as I need info from both the people table and > >> pets table?> Stuart, > > Thank you for the reply. I have been working in rails for around a year > so I am familiar with find. I was hoping for some info about SQL that I > could put in :conditions or just SQL for use in a find_by_sql to achieve > the effect that is described above. > > Any advice anyone?---- it helps if we had some code to work with so we know where you were going with this but something like... @found = Person.find(:all, :conditions => ["LOWER(first_name|last_name|pets.name) LIKE ?", ''%'' + params[:searchname].downcase + ''%''], :order => ''last_name ASC'', :limit => 8) note that the | is a concatenator for postgres where I think you would ''CONCAT'' in mysql The idea is that you concatenate all the possible match fields into one string and subsearch that. Craig
Craig White wrote:> On Mon, 2006-06-19 at 15:07 -0500, Matthew Margolis wrote: > >> Dark Ambient wrote: >> >>> I can''t write the code for you because I''m learning and new myself. >>> Forget joins if you set the relationships up in the models. >>> Very easy though , you''ll use the params method in your form, then >>> you''ll want to know everything about find() and it''s "conditions" >>> parameter. >>> At least that''s the place to start. >>> >>> Stuart >>> >>> On 6/19/06, Matthew Margolis <mrmargolis@wisc.edu> wrote: >>> >>>> I am working on writing a search method where a user can type a string >>>> of words and I return all the objects that have fields that match all of >>>> the words in one or a combination of fields. >>>> >>>> Person >>>> first_name >>>> last_name >>>> >>>> Pet >>>> name >>>> >>>> Person has_many :pets >>>> >>>> I want to write some SQL so that if I search for "Tony AAAA" I will get >>>> all the people who have Tony and AAA either in their first_name or >>>> last_name fields or in any of their pet''s name fields. For example, it >>>> would be ok to return a person if their first_name is Tony and they have >>>> a pet named AAAA. It would also be ok to return a person if their first >>>> name was Tony and last name was AAAA. >>>> >>>> I need for this to work for an arbitrary number of words in the search >>>> string so I assume that I will have to do some ruby string manipulation >>>> to modify the query. >>>> Any advice on how to start? I don''t know much SQL right now but I think >>>> a join would be used here as I need info from both the people table and >>>> pets table? >>>> > > >> Stuart, >> >> Thank you for the reply. I have been working in rails for around a year >> so I am familiar with find. I was hoping for some info about SQL that I >> could put in :conditions or just SQL for use in a find_by_sql to achieve >> the effect that is described above. >> >> Any advice anyone? >> > ---- > it helps if we had some code to work with so we know where you were > going with this but something like... > > @found = Person.find(:all, > :conditions => ["LOWER(first_name|last_name|pets.name) LIKE ?", > ''%'' + params[:searchname].downcase + ''%''], > :order => ''last_name ASC'', > :limit => 8) > > note that the | is a concatenator for postgres where I think you would > ''CONCAT'' in mysql > > The idea is that you concatenate all the possible match fields into one > string and subsearch that. > > Craig > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >A person can have many pets so just doing pets.name in the query won''t work for me. I need a way to search all the pet names for the pets that have person_id equal to the person I am currently searching in. right now I am doing something like this in my Person model def self.search_for_people(search_key, group) search_key.downcase! matches = Array.new for person in Group.find(group).people.find(:all) name = (person.first_name + person.last_name) pet_names= person.pets.map {|x| x.name}.join(" ") if (name.downcase.include?(search_key) || pet_names.downcase.include?(search_key) ) matches << person end end return matches end The above code works fine but only if the search_key is one word. It is also kind of string intensive, I think an SQL approach could be much faster. So to state my needs again, I want something that does what the above code does through SQL and if possible would work for a search string that is composed of many words where so long as all the words are found somewhere in a person and it''s pets we count that as a match. Thank you, Matthew Margolis blog.mattmargolis.net
On Mon, 2006-06-19 at 15:45 -0500, Matthew Margolis wrote:> Craig White wrote: > > On Mon, 2006-06-19 at 15:07 -0500, Matthew Margolis wrote: > > > >> Dark Ambient wrote: > >> > >>> I can''t write the code for you because I''m learning and new myself. > >>> Forget joins if you set the relationships up in the models. > >>> Very easy though , you''ll use the params method in your form, then > >>> you''ll want to know everything about find() and it''s "conditions" > >>> parameter. > >>> At least that''s the place to start. > >>> > >>> Stuart > >>> > >>> On 6/19/06, Matthew Margolis <mrmargolis@wisc.edu> wrote: > >>> > >>>> I am working on writing a search method where a user can type a string > >>>> of words and I return all the objects that have fields that match all of > >>>> the words in one or a combination of fields. > >>>> > >>>> Person > >>>> first_name > >>>> last_name > >>>> > >>>> Pet > >>>> name > >>>> > >>>> Person has_many :pets > >>>> > >>>> I want to write some SQL so that if I search for "Tony AAAA" I will get > >>>> all the people who have Tony and AAA either in their first_name or > >>>> last_name fields or in any of their pet''s name fields. For example, it > >>>> would be ok to return a person if their first_name is Tony and they have > >>>> a pet named AAAA. It would also be ok to return a person if their first > >>>> name was Tony and last name was AAAA. > >>>> > >>>> I need for this to work for an arbitrary number of words in the search > >>>> string so I assume that I will have to do some ruby string manipulation > >>>> to modify the query. > >>>> Any advice on how to start? I don''t know much SQL right now but I think > >>>> a join would be used here as I need info from both the people table and > >>>> pets table? > >>>> > > > > > >> Stuart, > >> > >> Thank you for the reply. I have been working in rails for around a year > >> so I am familiar with find. I was hoping for some info about SQL that I > >> could put in :conditions or just SQL for use in a find_by_sql to achieve > >> the effect that is described above. > >> > >> Any advice anyone? > >> > > ---- > > it helps if we had some code to work with so we know where you were > > going with this but something like... > > > > @found = Person.find(:all, > > :conditions => ["LOWER(first_name|last_name|pets.name) LIKE ?", > > ''%'' + params[:searchname].downcase + ''%''], > > :order => ''last_name ASC'', > > :limit => 8) > > > > note that the | is a concatenator for postgres where I think you would > > ''CONCAT'' in mysql > > > > The idea is that you concatenate all the possible match fields into one > > string and subsearch that. > > > > Craig > > > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > A person can have many pets so just doing pets.name in the query won''t > work for me. I need a way to search all the pet names for the pets that > have person_id equal to the person I am currently searching in. > right now I am doing something like this in my Person model > > def self.search_for_people(search_key, group) > search_key.downcase! > matches = Array.new > for person in Group.find(group).people.find(:all) > name = (person.first_name + person.last_name) > pet_names= person.pets.map {|x| x.name}.join(" ") > if (name.downcase.include?(search_key) || > pet_names.downcase.include?(search_key) ) > matches << person > end > end > return matches > end > > The above code works fine but only if the search_key is one word. It is > also kind of string intensive, I think an SQL approach could be much faster. > So to state my needs again, I want something that does what the above > code does through SQL and if possible would work for a search string > that is composed of many words where so long as all the words are found > somewhere in a person and it''s pets we count that as a match.---- I will give you the best solution to complicated finds that I have run into - (yeah Ezra) ez_where http://brainspl.at/articles/2006/01/30/i-have-been-busy I was going to mention it last time but I didn''t want to confuse the issue. This tool has allowed me to simplify the much messy finds. I think he has updated since then but Ezra has a lot of irons in the fire so you might want to download the latest plugin... Craig
On Jun 19, 2006, at 11:56 AM, Matthew Margolis wrote:> I am working on writing a search method where a user can type a > string of words and I return all the objects that have fields that > match all of the words in one or a combination of fields. > > Person > first_name > last_name > > Pet > name > > Person has_many :pets > > I want to write some SQL so that if I search for "Tony AAAA" I will > get all the people who have Tony and AAA either in their first_name > or last_name fields or in any of their pet''s name fields. For > example, it would be ok to return a person if their first_name is > Tony and they have a pet named AAAA. It would also be ok to return > a person if their first name was Tony and last name was AAAA. > > I need for this to work for an arbitrary number of words in the > search string so I assume that I will have to do some ruby string > manipulation to modify the query. > Any advice on how to start? I don''t know much SQL right now but I > think a join would be used here as I need info from both the people > table and pets table? > > Thank you, > Matthew Margolis > blog.mattmargolis.net >Mathew- See if this works for you. Download the latest ez_where plugin from here: script/plugin install svn://rubyforge.org//var/svn/ez-where THen: @results = Person.find_where(:all, :include => :pets) do |person, pet| any { person.any_of(:first_name, :last_name) =~ "%#{params [:search_term]}%" pet.name =~ "%#{params[:search_term]}%" } end Cheers- -Ezra -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060619/f9f8236b/attachment.html