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!

I've done something like this [answer][1] before. [1]: http://stackoverflow.com/questions/2381718/rails-activerecord-date-between — Adam Sommer, Sep 24 '15 at 14:53

score 2 · Accepted Answer · answered Sep 24 '15 at 14:56

2

You can use the following:

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

answered Sep 24 '15 at 14:56

MrYoshiji

54,334
13
124
117

Thank you very much, @MrYoshiji! – Brit200313 Sep 24 '15 at 15:03

score 0 · Answer 2 · answered Sep 24 '15 at 15:07

0

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)

answered Sep 24 '15 at 15:07

j-dexx

10,286
3
23
36

penner · Answer 3 · 2015-09-24T15:00:16.643

-1

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.

edited Sep 24 '15 at 15:00

answered Sep 24 '15 at 14:51

penner

2,707
1
37
48

Thanks @penner but is being handled in the controller. – Brit200313 Sep 24 '15 at 15:04

Select object based on date range

3 Answers3