CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Michael
Michael

Reputation: 1507

How to combine AND and OR operators using ActiveRecord?

I need to make SQL requests that would implement this behavior:

SELECT * FROM table WHERE (created_at > '2010/01/01' AND created_at < '2010/12/01') OR (created_at > '2012/01/01' AND created_at < '2012/12/31')

The goal is to have a request that returns results from a list of selected years (that might not be consecutive, we might have 2010 and 2012 without 2011).

Any clue how I would achieve that ?

Upvotes: 0

Views: 153

Answers (4)

IcyBright
IcyBright

Reputation: 664

Use Arel

t = User.arel_table

results = User.where(t[:created_at].gt( '2010-01-01').and(t[:created_at].lt('2010-12-01')).
or(t[:created_at].gt('2012-01-01').and(t[:created_at].lt('2012-12-31'))))

Upvotes: 1

apneadiving
apneadiving

Reputation: 115521

Using the any_of gem:

Table.where.any_of(Table.where(created_at: t1..t2), Table.where(created_at: t3..t4))

t1, t2, t3 and t4, being the dates.

Upvotes: 0

Arthur
Arthur

Reputation: 644

I prefer using:

where('created_at > ? AND created_at < ? OR created_at > ? AND created_at < ?', Time.new(2010,1,1), Time.new(2010,12,1), Time.new(2010,1,1), Time.new(2012,1,1), Time.new(2012,12,31))

Upvotes: 0

AJFaraday
AJFaraday

Reputation: 2450

You could try some SQL in the where method

Table.where(
  'created_at between ? and ? or created_at between ? and ?', 
  date1,
  date2,
  date3,
  date4
)

Also, as a useful hint for debugging these queries. Once you've got your query, you can call to_sql to see the literal SQL result.

User.where(admin: true).to_sql

Upvotes: 1

Related Questions