Rails 5 ActiveRecord - combine OR adn AND clauses

Question

I can't figure out the right syntax to use when including several models and using AND or OR clauses. For example, there Shop model that has_one relation with Address model and belongs_to with Country. How for example add OR to the below query:

Shop.includes(:address, :country)

Trying like this:

Shop.includes(:address, :country).where('countries.code'=> 'FR').and('counties.updated_at > ?', Date.today.days_ago(7))

raises the error:

NoMethodError: undefined method `and' for #<Shop::ActiveRecord_Relation:0x00007fb90d0ea3f8>

I found this thread at SO, but in this case, I have to repeat the same where clause before each OR statement? - looks not so DRY :( What am I missing ?

score 1 · Answer 1 · answered Mar 12 '19 at 17:01

1

Don't kick yourself... you don't need to use and at all, just string another where in:

Shop.includes(:address, :country).where('countries.code'=> 'FR').where('counties.updated_at > ?', Date.today.days_ago(7))

answered Mar 12 '19 at 17:01

Mark

6,112
4
21
46

thank you, it generates `AND` clause, but what about `OR`? – belgoros Mar 12 '19 at 17:05
This will work : `shops = Shop.includes(:country, :address).where('countries.code'=> 'BE').or(Shop.includes(:country, :address).where('countries.updated>?', Date.today.days_ago(10)))` but too verbose and DRY. – belgoros Mar 12 '19 at 17:08
That looks as clean as you can get.. maybe split them both up into two different variables to make it look a bit nicer but I can't think of a quicker way than that – Mark Mar 12 '19 at 17:21
the 'OR' method works as you'd expect, and when you want an 'AND' just replace it with another 'where' – Mark Mar 12 '19 at 17:21
Ok, thank you, may be extract to a separate scope will make it DRYer... – belgoros Mar 12 '19 at 17:31

score 0 · Answer 2 · answered Mar 12 '19 at 22:22

There is a better solution if you need to add multiple OR clause to AND clause. To get around it, there is arel_table method that can be used as follows. Let's say we have the following models

Shop -> has_one :address
Shop -> belongs_to :country

and we would like to find all the shops by country code and address updated_at or country updated_at should be greater then a date you pass in:

some_date = Date.today
countries = Country.arel_table
addresses = Address.arel_table

# creating a predicate using arel tables

multi_table_predicate = countries[:updated_at].gt(some_date).or(addresses[:updated_at].gt(some_date))

# building the query

shops = Shop.includes(:address, :country).where(countries: {code: 'FR'}).where(multi_table_predicate)

This will execute a LEFT OUTER JOIN and here is where clause:

WHERE "countries"."code" = $1 AND ("countries"."updated_at" > '2019-03-12' OR "addresses"."updated_at" > '2019-03-12')

Sure, you can chain more tables and multiply OR conditions if you want.

Hope this helps.

Rails 5 ActiveRecord - combine OR adn AND clauses

2 Answers2