# SQL Search Qustion

**URL:** <https://www.ruby-forum.com/t/sql-search-qustion/63840>\
**Category:** Rails\
**Created:** [June 19, 2006, 8:57pm UTC](https://www.ruby-forum.com/t/sql-search-qustion/63840 "2006-06-19T20:57:23Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![mansfiem](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@mansfiem](https://www.ruby-forum.com/u/mansfiem)\
**Post date:** [June 19, 2006, 8:57pm UTC](https://www.ruby-forum.com/t/sql-search-qustion/63840/1 "2006-06-19T20:57:23Z")

</div>

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](http://blog.mattmargolis.net)

---

<div class="post-metadata">

**Author:** ![mansfiem](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@mansfiem](https://www.ruby-forum.com/u/mansfiem)\
**Post date:** [June 19, 2006, 9:16pm UTC](https://www.ruby-forum.com/t/sql-search-qustion/63840/2 "2006-06-19T21:16:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mansfiem](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@mansfiem](https://www.ruby-forum.com/u/mansfiem)\
**Post date:** [June 19, 2006, 10:09pm UTC](https://www.ruby-forum.com/t/sql-search-qustion/63840/3 "2006-06-19T22:09:02Z")

</div>

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.  
> > removed\_email\_address@domain.invalid  
> > [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](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](http://blog.mattmargolis.net)

---

<div class="post-metadata">

**Author:** ![mansfiem](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@mansfiem](https://www.ruby-forum.com/u/mansfiem)\
**Post date:** [June 19, 2006, 10:22pm UTC](https://www.ruby-forum.com/t/sql-search-qustion/63840/4 "2006-06-19T22:22:31Z")

</div>

On Mon, 2006-06-19 at 15:07 -0500, Matthew M. wrote:

> > On 6/19/06, Matthew M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![mansfiem](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@mansfiem](https://www.ruby-forum.com/u/mansfiem)\
**Post date:** [June 19, 2006, 11:52pm UTC](https://www.ruby-forum.com/t/sql-search-qustion/63840/5 "2006-06-19T23:52:18Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![mansfiem](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@mansfiem](https://www.ruby-forum.com/u/mansfiem)\
**Post date:** [June 19, 2006, 10:46pm UTC](https://www.ruby-forum.com/t/sql-search-qustion/63840/6 "2006-06-19T22:46:06Z")

</div>

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  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](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](http://blog.mattmargolis.net)

---

<div class="post-metadata">

**Author:** ![mansfiem](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@mansfiem](https://www.ruby-forum.com/u/mansfiem)\
**Post date:** [June 20, 2006, 11:31am UTC](https://www.ruby-forum.com/t/sql-search-qustion/63840/7 "2006-06-20T11:31:43Z")

</div>

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](http://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