LIKE clause in rails

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

Peace,
Phillip

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

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

Thanks,
Phillip

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

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

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

Peace,
Phillip