Rails 3 Query Help

Question

How can I use LIKE in a query? The reason I want to do this is because I have this query that checks to make sure the form type AND state are correct, but I would like it to not be case sensitive. Here is what I have

@fields = Field.where(:form_type => 1, :state => "Alaska")

Also how can I make it so that if the form_type = 1 OR form_type = * it still returns results?

Answer 1

If you don't like writing raw SQL in your code, but you want to use LIKE clauses in your queries, the squeel gem is very nice. http://metautonomo.us/projects/squeel/

I have used the gem with MySQL and PostreSQL and it generates the proper SQL (LIKE for MySQL and ILIKE for Postgres). Your query with squeel would look something like this:

@fields = Field.where{(form_type.in [1,2,3]) & (state =~ 'Alaska'")} 

Answer 2

If you want it to be case insensitive and allow form_type to be 1 or 3, you could do it like this:

@fields = Field.where(:form_type => [1,3]).where("LOWER(state) = 'alaska'")

Or, if you like shouting (and who doesn't like a bit of shouting now and then?):

@fields = Field.where(:form_type => [1,3]).where("UPPER(state) = 'ALASKA'")

The individual .where calls are combined with AND and the most reliable way to get consistent case behavior is to do it yourself.

Answer 3

You would have to use this format:

@fields = Field.where(:form_type => 1).where("state LIKE 'Alaska'")