LIKE clause in rails

rubyonrails-talk
1

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

Peace, Phillip

2

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

3

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

Thanks, Phillip

4

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

5

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

6

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

Peace, Phillip