LIKE clause in rails

Rails
1

Hello,

I am trying to achieve a search functionality in the following manner

i have an table called people
which has
first_name,last_name,etc…
After the user form to search people is submitted i get the params hash
as params[:persons]={“first_name”=> “%John%”,“last_name”=>"%Stevens%"}

NOTE : I have changed the params hash to include the %.

Now i want to search as

@people = Person.find_all(params[:person])

IN the development log I get the query as

SELECT * FROM people WHERE (people.[first_name] = ‘%John%’ AND
people.[last_name] = ‘%Stevens%’)

The only thing i need to do is replace the =(EQUAL TO) sign by LIKE

Please help me achieve this. This would prevent me to search each column
in the table individually as there are a lot of columns in the table.

Thank you.
Regards,
Ank

2

One thing I want to touch on really quick not directly related to your
main question is your use of:

@people = Person.find_all(params[:person])

This is deprecated. May I recommend using the replacement:

@people = Person.find(:all, :conditions => params[:person])

Won’t fix your problem but still a good idea. I don’t know any
particularly easy way to do “LIKE” queries using the built in find with
a conditions hash. I usually have to move to using a conditions string
that I build manually.

Hopefully someone else has a particularly good solution. I know there
are search plugins which are rather good such as acts_as_ferret but that
seems like overkill for your name search.

3

On Dec 28, 2007, at 1:15 PM, Nathan E. wrote:

Won’t fix your problem but still a good idea. I don’t know any
particularly easy way to do “LIKE” queries using the built in find
with
a conditions hash. I usually have to move to using a conditions string
that I build manually.

I build my own SQL string and params hash and pass them to find_by_sql.

Peace,
Phillip

4

Phillip K. wrote:

On Dec 28, 2007, at 1:15 PM, Nathan E. wrote:

Won’t fix your problem but still a good idea. I don’t know any
particularly easy way to do “LIKE” queries using the built in find
with
a conditions hash. I usually have to move to using a conditions string
that I build manually.

I build my own SQL string and params hash and pass them to find_by_sql.

Peace,
Phillip

Hi,
The problem is i am using the complete object to find people to find and
not its columns. So can you tell me how do i proceed if i need it to do
this your way.
My table contains many columns so i want to work with the complete
object peeple

5

On Dec 28, 2007, at 1:45 PM, Ank Ag wrote:

not its columns. So can you tell me how do i proceed if i need it
to do
this your way.
My table contains many columns so i want to work with the complete
object peeple

Hm. You could try something like this:

def self.full_search(params)
sql = "
select *
from people
where true
"
params_hash = {}

params.each_pair do |key, value|
if !value.blank?
sql += "
and #{key.to_s} like :#{key.to_s}
"

  params_hash[key] = "%#{value}%"
end

end

find_by_sql([sql, params_hash])
end

I haven’t tried this, but an approach like this should work. Since
you are getting the column names in the params hash, it should be
pretty straight forward.

Put that in your People model and call it like this

@people = People.full_search(params)

Peace,
Phillip

6

Phillip K. wrote:

On Dec 28, 2007, at 1:45 PM, Ank Ag wrote:

not its columns. So can you tell me how do i proceed if i need it
to do
this your way.
My table contains many columns so i want to work with the complete
object peeple

Hm. You could try something like this:

def self.full_search(params)
sql = "
select *
from people
where true
"
params_hash = {}

params.each_pair do |key, value|
if !value.blank?
sql += "
and #{key.to_s} like :#{key.to_s}
"

  params_hash[key] = "%#{value}%"
end

end

find_by_sql([sql, params_hash])
end

I haven’t tried this, but an approach like this should work. Since
you are getting the column names in the params hash, it should be
pretty straight forward.

Put that in your People model and call it like this

@people = People.full_search(params)

Peace,
Phillip

Hi,
Thanks for your effort. Seems to get close i am getting the following
error
after implementing your piece of code.

missing value for :first_name in
select *
from people
where true

    and first_name like :first_name
    and last_name like :last_name

Please suggest me what is to be done.
thanks
Regards,
Ank

7

Phillip K. wrote:

On Dec 28, 2007, at 2:32 PM, Ank Ag wrote:

    and first_name like :first_name
    and last_name like :last_name

Please suggest me what is to be done.
thanks
Regards,
Ank

Will you please post your code as you have implemented it?

Thanks,
Phillip

class Person < ActiveRecord::Base
belongs_to :relation
belongs_to :function
belongs_to :category
has_and_belongs_to_many :mailinglists
has_and_belongs_to_many :organisations

def self.full_search(params)
sql = "
select *
from people
where true
"
params_hash = {}

params.each_pair do |key, value|
if !value.blank?

  sql += "
    and #{key.to_s} like :#{key.to_s}
  "


  params_hash[key] = "%#{value}%"
end

end

find_by_sql([sql, params_hash])
end
end

In person_controller

 params[:person].delete_if {|key, value| value=="" }
 params[:person].each{|k,v| params[:person][k] = '%' + v + '%'}
 @people = Person.full_search(params[:person])

Thank you.
Regards,
Ank

8

On Dec 28, 2007, at 6:13 PM, Ank Ag wrote:

from people

In person_controller

 params[:person].delete_if {|key, value| value=="" }
 params[:person].each{|k,v| params[:person][k] = '%' + v + '%'}
 @people = Person.full_search(params[:person])

Thank you.
Regards,
Ank

I’ll have to do a little testing, but off the top, you won’t need to
do those first two params manipulations in your controller. If the
code that I supplied does, in fact, work, it will take care of both
of those cases.

Oh, and another thought that I had after I posted that: This
approach will only work on string fields that a LIKE makes sense on
to begin with.

I’ll see what I can come up with.

Peace,
Phillip

9

Okie dokie. Apparently, there’s something funny going on between the
keys of a Hash and a HashWithIndifferentAccess. When iterating over
the pairs in the each_pair block, the keys are strings, not symbols.
That’s what tripped it up. So here is a working copy, with a
modification to ignore controller and action.

def self.full_search(params)
params.delete_if { |key, value| [:controller, :action].include?
key.to_sym }

sql = "
  select *
  from people
  where true
"
params_hash = {}

params.each_pair do |key, value|
  if !value.blank?
    sql += "
      and #{key} like :#{key}
    "

    params_hash[key.to_sym] = "%#{value}%"
  end
end

find_by_sql([sql, params_hash])

end

Remember, you don’t need those two lines in your controller. In
fact, the second one will mess you up in the search.

Peace,
Phillip

10

Thank you sooooooo much…It Works. Thanks once again.you r a
genius.

11

On Dec 28, 2007, at 2:32 PM, Ank Ag wrote:

    and first_name like :first_name
    and last_name like :last_name

Please suggest me what is to be done.
thanks
Regards,
Ank

Will you please post your code as you have implemented it?

Thanks,
Phillip

12

On Dec 28, 2007, at 7:11 PM, Ank Ag wrote:

Thank you sooooooo much…It Works. Thanks once again.you r a
genius.

Hah hah. Far from it. Glad it worked for you, though.

Peace,
Phillip