How do I do an OR SQL search with Ruby on Rails?

Question

Hi There,

I am just beginning to learn Ruby on Rails, and I haven't been able to find the answer to this question.

I have the following code:

@products = Product.find(:all,
  :conditions => ["productsubtype_id = ?", @productsubtypes],
  :order => "name")

@productsubtypes is an array (currently containing two objects from another SQL query, almost identical to this one) - the SQL it generates is the following:

SELECT * FROM `products` WHERE (productsubtype_id = 2,3)  ORDER BY name

As you can see, the above is not valid (at least, not for MySQL) - I need to find a way to change the original Ruby code to generate the following SQL code (or something close to it:

SELECT * FROM `products` WHERE (productsubtype_id = 2 
  OR productsubtype_id = 3)  ORDER BY name

Is there a way to change the original code to do what I need, or am I on completely the wrong track here?

Thanks for your help,

Juan

Answer 1

Try using the conditions_fu plugin.

Product.all(:conditions => { :productsubtype_id.in => @productsubtypes },
:order => "name")

Answer 2

@products = Product.find(:all,

:conditions => ["productsubtype_id IN (?)", @productsubtypes],

:order => "name")

Answer 3

These solutions both work, but ActiveRecord already has built-in support for knowing if you're passing an array or a single id when using a conditions hash. Example:

User.all(:conditions => {:id => [1,2,3]})

generates the query

SELECT * FROM `users` WHERE (users.`id` IN( 1,2,3 ))

so you would want

@products = Product.find(:all,
  :conditions => {:productsubtype_id => @productsubtypes},
  :order => "name")

for more complex queries, though, you either need to pass sql in conditions or use a plugin (i.e. conditions_fu)

Answer 4

Also have to consider the use of IN vs EXISTS. If the subset you are using within the IN is very large, using an EXISTS might be better. Basically, one uses an join, the other does not.