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 M.
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
Dark A. wrote:
I am working on writing a search method where a user can type a string
Person has_many :pets
to modify the query.
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails
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?
Matthew M.
blog.mattmargolis.net
On Mon, 2006-06-19 at 15:07 -0500, Matthew M. wrote:
On 6/19/06, Matthew M. [email protected] wrote:
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
On Mon, 2006-06-19 at 15:45 -0500, Matthew M. wrote:
At least that’s the place to start.
first_name
would be ok to return a person if their first_name is Tony and they have
‘CONCAT’ in mysql
pet_names= person.pets.map {|x| x.name}.join(" ")
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
Craig W. wrote:
last_name
a pet named AAAA. It would also be ok to return a person if their first
it helps if we had some code to work with so we know where you were
The idea is that you concatenate all the possible match fields into one
string and subsearch that.
Craig
Rails mailing list
[email protected]
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 M.
blog.mattmargolis.net
On Jun 19, 2006, at 11:56 AM, Matthew M. wrote:
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 M.
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