CODEWITHSUNDEEP
ruby-on-rails
user96043
user96043

Reputation:

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

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

Upvotes: 2

Views: 197

Answers (4)

semanticart
semanticart

Reputation: 5464

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)

Upvotes: 3

Sunil
Sunil

Reputation:

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.

Upvotes: 1

Reuben Mallaby
Reuben Mallaby

Reputation: 5761

@products = Product.find(:all,

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

:order => "name")

Upvotes: 1

besen
besen

Reputation: 32480

Try using the conditions_fu plugin.

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

Upvotes: 1

Related Questions