Checking if a boolean column is true in MySQL/Rails

Question

Rails and MySQL:

I have a table with several boolean columns representing tags. I want to find all the rows for which a specific one of these columns is 'true' (or I guess in the case of MySQL, '1'). I have the following code in my view.

@tag = params[:tag]

@supplies = Supply.find(:all,
  :conditions=>["? IS NOT NULL and ? !=''", @tag, @tag], :order=>'name')

The @tag is being passed in from the url. Why is it then that I am instead getting all of my @supplies (i.e. every row) rather than just those that are true for the column for @tag.

Thanks!

Answer 1

If params[:tag] is set to foo, the find method is generating this query:

select * from supplies where 'foo' is not null and 'foo' != '' order by name;

This is returning all your Supply records because both conditions are always true.

1. 'foo' is not null
2. 'foo' != ''

Of course you're intending for params[:tag] to be a column name, but that is just terrible design.

You should have a tag attribute on your Supply model and use the following finder:

@supplies = Supply.all(:conditions => ["tag = ?", params[:tag]], :order => "name")

---

If you really want the ability for Supplies to have the option for multiple Tags, use:

class Supply < ActiveRecord::Base
  has_and_belongs_to_many :tags
end

class Tag < ActiveRecord::Base
  has_and_belongs_to_many :supplies
end

@supplies = Supplies.all(:conditions => {:tags => ['foo', 'bar']}, :order => "name")

Answer 2

I think this is what you want to do

@tag = params[:tag]
@supplies = Supply.find(:all, :conditions=>["? = ?", @tag, true], :order=>'name')