How can I use the Rails "find" method to perform an AND or an OR query?

Question

Let's assume I have a model called "product." Let's assume that product has three fields. These fields are 'name' (type string), 'cost' (type integer), and 'is_visible' (type bool).

1) How can I do a search query using the Rails "find" method (if there is another method, that's fine) so that I can search for all products with a cost greater than 100 AND is_visible is true?

2) What if we wanted to change this to search to where name != '' OR cost == 0?

This isn't a problem to do an SQL, but I would like to think that Rails has a way to do AND/OR queries to the database without using SQL.

Thanks!

Answer 1

You would need to use the conditions option on the find method. The conditions option can be either a Hash, Array, or String. There are lots of options for conditions, so I recommend reading the API help for it. For example if you want to (1):

Product.find(:all, :conditions => ['cost > ? and is_visible is true', 100])

Or (2)

Product.find(:all, :conditions => ["name != '' or cost =0])

Answer 2

This is exactly the problem SQL was designed to solve, so why not to use it? Just add an appropriate :condition and your problem is solved.

Answer 3

If you want something like LINQ you can check alternative Ruby ORMs like DataMapper or Sequel that provide more complex filtering capabilities.

For example in Sequel 2 you can write:

items.filter((:cost > 100) & (:is_visible = 1))

You can also use the bitwise "|" operator to get OR condition.

In DataMapper this will look like:

Model.all(:cost.gt => 100, :is_visible.eq => 1)

However some people don't like the fact that those features are implemented by overloading the standard Symbol class.