ActiveRecord OR query for daterange

Question

I'm trying to get books ActiveRecord objects that have a 'from or 'to' between a date range (start_date..end_date).

This code works just fine:

Book.where(from: start_date..end_date)

But I want to use 'or' in my statement and this code fails:

Book.where("from: ? OR to: ?", (start_date..end_date), (start_date..end_date))

Please help me to find my mistake.

[May be of help](http://stackoverflow.com/q/31096009/3444240) — potashin, Jun 19 '16 at 00:44

dp7 · Accepted Answer · 2016-04-05T07:53:35.157

1

You can try this -

Book.where("books.from BETWEEN ? AND ? OR books.to BETWEEN ? AND ?", start_date,end_date,start_date,end_date)

edited Apr 05 '16 at 07:53

answered Apr 05 '16 at 07:43

dp7

It is almost right. I get SELECT `books`.* FROM `books` WHERE (from BETWEEN '2016-02-01' AND '2016-02-29' OR to BETWEEN '2016-02-01' AND '2016-02-29'). But a little part is still missing (should be `books`.`from` BETWEEN ... AND ...) – Nadiya Apr 05 '16 at 07:52

Paulo Fidalgo · Answer 2 · 2016-07-06T13:21:42.187

Check the Rails guides on Range conditions:

Client.where(created_at: (Time.now.midnight - 1.day)..Time.now.midnight)

That will produce the following SQL:

SELECT * FROM clients WHERE (clients.created_at BETWEEN '2008-12-21 00:00:00' AND '2008-12-22 00:00:00')

So given in you can use or in ActiveRecord in the Rails 5, you can do

Book.where(from: start_date..end_date).or(Book.where(from: new_start_date..new_end_date))

Notes: You don't need to use rails 5 to use this OR query. We can also used it with rails 4.2, thanks to gem where-or

z.shan · Answer 3 · 2016-04-05T07:51:02.340

0

 if from < to
    Book.where("from >= #{start_date} OR to  <= #{end_date}")
 else 
  // do something else
 end

edited Apr 05 '16 at 07:51

answered Apr 05 '16 at 07:45

z.shan

'from' should be less than 'end_date' & 'to' should' be bigger than 'start_date' too, I explained it in my question – Nadiya Apr 05 '16 at 07:47
you can define a check to do this – z.shan Apr 05 '16 at 07:49

3 Answers3