1

I am using Ruby on Rails 3.2.2 and, given I have stated all needed classes/models involved in this question, I would like to build the following SQL query statement (note the SQL OR) "a là Ruby on Rails way":

SELECT customers.* FROM customers WHERE (firstname = 'James' OR firstname = 'Paula') AND lastname = 'Brown'

Is it possible by using Rails facilities?

user12882
  • 4,702
  • 9
  • 39
  • 54
  • possible duplicate of [How to use OR condition in ActiveRecord query](http://stackoverflow.com/questions/10871131/how-to-use-or-condition-in-activerecord-query) – tokland Jun 05 '12 at 19:10
  • @tokland I don't think so. See my answer(and others). – Anil Jun 05 '12 at 22:52
  • @Anil: well, I understood the point was how to make a OR, not that the field was the same (then indeed it's ok a `IN (?)`). – tokland Jun 05 '12 at 23:07

3 Answers3

1

A nice way to do that, if you don't want to use any external gem like squeel, is to build a scope for the purpose: 

scope :by_first_and_last_name, ->(firsts, last) { 
   where(['firstname in (?) AND lastname = ?', firsts, last])
}

Since it returns an Arel you can chain it normally with other scopes.

You can use it like this: 

YourModel.by_first_and_last_name(['Foo','Bar'], 'Baz').first
Fabrizio Regini
  • 1,500
  • 13
  • 26
  • 2
    metawhere is rails 2, you are looking for Ernie's rewrite called squeel https://github.com/ernie/squeel. On this same topic Ryan Bates makes some interesting points about this in a recent Railscast (pro) http://railscasts.com/episodes/355-hacking-with-arel – Derek Hall Jun 05 '12 at 18:47
  • 1
    @Fabrizio He is looking for (firstname or firstname) AND lastname. – Anil Jun 05 '12 at 19:51
  • @Anil sorry I misread the question. Just to be consistent I'll update my answer, but the IN approach is clearly a better one. – Fabrizio Regini Jun 05 '12 at 20:08
1

The following code would allow you the same effectiveness as multiple ORs on the firstname field:

Customer.where(['firstname IN (?) AND lastname = ?', ['James', 'Paula'], 'Brown'])
Supy
  • 180
  • 2
  • 10
0

You are not really looking for an OR. Rather, you are looking for an IN.

SELECT customers.* FROM customers WHERE firstname IN ('James','Paula') AND lastname = 'Brown'

This has been supported in Rails for a long time(may be since its inception). You can specify an array of values instead of a single value in your finds.

@firstnames = ['James','Paula']
@lastname = 'Brown'
@customers = Customer.find_all_by_firstname_and_lastname(@firstnames,@lastname)
Anil
  • 3,899
  • 1
  • 20
  • 28
  • Those finders are going to be deprecated in Rails 4. You should instead use `Customer.where(first_name: @firstnames, last_name: @lastname)` – mesozoic Jan 30 '13 at 15:53