5

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!

JP Richardson
  • 38,609
  • 36
  • 119
  • 151

3 Answers3

13

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])
scottd
  • 7,364
  • 1
  • 24
  • 27
  • According to rails api doc it should 'be = true' but Adam you seem to have missed the point in your answers and here that it's a Rails question no a SQL question. For many of us the Rails way is just easier and better. – allesklar Nov 13 '08 at 18:26
  • 2
    Actually everything you include in :conditions will be embedded directly into the SELECT statement generated by ActiveRecord - so this definitely has something to do with SQL. – Adam Byrtek Nov 13 '08 at 23:22
  • I think the .where method is more appropriate now. http://stackoverflow.com/questions/9574659/rails-where-vs-find – Vic Boudolf Sep 14 '15 at 14:52
4

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.

Adam Byrtek
  • 12,011
  • 2
  • 32
  • 32
0

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.

Adam Byrtek
  • 12,011
  • 2
  • 32
  • 32