Rails scope filter by date range

1
votes

There are many questions relate to rails date range problem but mine is a little more complicated.

I have two models: house and booking. A House has_many bookings. A Booking has two attributes in date format: check_in and check_out.

What I want to achieve: Giving a valid date range, show all houses that are available during this range. In detail:

  • The start date of the range should not be in any booking.
  • The end date of the range should not be in any booking.
  • There should not be any booking between the start and the end.

Can this be done using the rails scope?

UPDATE:

I found the code below that can check scope date interval that overlaps. 

named_scope :overlapping, lambda { |interval| {
:conditions => ["id <> ? AND (DATEDIFF(start_date, ?) * DATEDIFF(?, end_date)) >= 0", interval.id, interval.end_date, interval.start_date]
}}

How can I transfer this to my problem?

2
ruby-on-railsrubyrails-activerecord
You should probably use the Bookable Gem. - Antarr Byrd
@AntarrByrd Thanks for the suggestion. I already have my own model and I want to keep it simple and just do the scope. - Haobo Chen
You could always have a look at how it is done there - Antarr Byrd

2 Answers

1
votes
scope :overlapping, (lambda do |start_date, end_date|
  House.includes(:bookings).where("bookings.check_in < ? AND bookings.check_out > ?", 
  start_date, end_date).references(:bookings).uniq
end)

I went ahead and deleted the >= and <= operators in favor of > and < to explicitly show these bookings being outside of the given range, but you can adjust them per your needs!

Update

Changed query to use #includes instead of #joins, since we're querying the attached table.

1
votes

Yes it is possible to have this query through scope. Put this scope in house model.

scope :overlapping, -> (start_date, end_date) { 
  includes(:bookings).where('bookings.check_in < ? AND bookings.check_out > ?',
  start_date.to_date, end_date.to_date)
}

And call as House.overlapping('2015-07-01', '2015-07-09')