Select object based on date range

Question

Tech specs: ruby 2.1.5p273, Rails 4.2.3.

I have an array of Days that I want to loop through to pick the right Exits (model) that fall within a date range.

• @exits has :start_date and :end_date

• @days is an array of dates like:

  => [Sun, 06 Sep 2015, Sat, 12 Sep 2015, Tue, 15 Sep 2015, Fri, 18 Sep 2015, Sat, 19 Sep 2015, Sun, 20 Sep 2015, Wed, 23 Sep 2015]

I thought something like this would work:

@days.each do |day|
  @exits.where(:start_date..:end_date).include?(day)
end

but I get an error:

TypeError: Cannot visit Range

What is the best way to query an object that has a date range (between two fields) by comparing it against a single date? Thanks!

Answer 1

If you don't want to loop over them then you can do:

Event.where("start_date IN (:days) AND end_date IN (:days)", { days: @days })

or

Event.where(start_date: @days, end_date: @days)

Answer 2

Exit.where(day: @exit.start_date..@exits.end_date)

or 

Exit.where('day >= ? AND day <= ?', @exit.start_date, @exits.end_date)

Doing SQL queries in a loop is probably a bad idea, it could be refactored to be be one call most likely. And this should happen in the controller not in the view.

Answer 3

You can use the following:

@days.each do |day|
  exits = Exit.where('? BETWEEN start_date AND end_date', day)
  # etc.
end