thr3ads.net - Rails - LIKE clause in rails [Dec 2007]

If this information is useful, please help other people find it:
Share via:

Ank Ag

2007-Dec-28 19:00 UTC

LIKE clause in rails

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
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Nathan Esquenazi

2007-Dec-28 19:15 UTC

head link

Re: LIKE clause in rails

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.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Phillip Koebbe

2007-Dec-28 19:36 UTC

head link

Re: LIKE clause in rails

On Dec 28, 2007, at 1:15 PM, Nathan Esquenazi wrote:
>
> 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.
>
I build my own SQL string and params hash and pass them to find_by_sql.

Peace,
Phillip


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Ank Ag

2007-Dec-28 19:45 UTC

head link

Re: LIKE clause in rails

Phillip Koebbe wrote:> On Dec 28, 2007, at 1:15 PM, Nathan Esquenazi 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
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Phillip Koebbe

2007-Dec-28 20:04 UTC

head link

Re: LIKE clause in rails

On Dec 28, 2007, at 1:45 PM, Ank Ag wrote:
>
> Phillip Koebbe wrote:
>> On Dec 28, 2007, at 1:15 PM, Nathan Esquenazi 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
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
> -- 
> Posted via http://www.ruby-forum.com/.
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Ank Ag

2007-Dec-28 20:32 UTC

head link

Re: LIKE clause in rails

Phillip Koebbe 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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Phillip Koebbe

2007-Dec-28 22:24 UTC

head link

Re: LIKE clause in rails

On Dec 28, 2007, at 2:32 PM, Ank Ag wrote:>
> 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
>
Will you please post your code as you have implemented it?

Thanks,
Phillip


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Ank Ag

2007-Dec-29 00:13 UTC

head link

Re: LIKE clause in rails

Phillip Koebbe 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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Phillip Koebbe

2007-Dec-29 00:26 UTC

head link

Re: LIKE clause in rails

On Dec 28, 2007, at 6:13 PM, Ank Ag wrote:
> 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
>
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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Phillip Koebbe

2007-Dec-29 00:54 UTC

head link

Re: LIKE clause in rails

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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Ank Ag

2007-Dec-29 01:11 UTC

head link

Re: LIKE clause in rails

> 


Thank you sooooooo much.......It Works. Thanks once again.you r a
genius.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Phillip Koebbe

2007-Dec-29 01:58 UTC

head link

Re: LIKE clause in rails

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Maybe Matching Threads

Search for more seemingly similar threads

Rails - Dec 2007 - LIKE clause in rails

LIKE clause in rails

Re: LIKE clause in rails

Re: LIKE clause in rails

Re: LIKE clause in rails

Re: LIKE clause in rails

Re: LIKE clause in rails

Re: LIKE clause in rails

Re: LIKE clause in rails

Re: LIKE clause in rails

Re: LIKE clause in rails

Re: LIKE clause in rails

Re: LIKE clause in rails

Maybe Matching Threads