SQL Search Qustion

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

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs