CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
mikker
mikker

Reputation: 831

Finding records that overlap a range in Rails

So, I have an Event model that has a starts_at and a ends_at column and I want to find events that take place in a range of dates.

I've come up with this named_scope (range is typically a month):

named_scope :in_range, lambda { |range|
  {:conditions => [
    'starts_at BETWEEN ? AND ? OR ends_at BETWEEN ? AND ?',
    range.first, range.last, range.first, range.last]} }

Which works as expected.

But if an event starts the month before and ends the month after the range it won't show. Is there any way to find those events in a proper way?

Upvotes: 15

Views: 2491

Answers (2)

Mladen Jablanović
Mladen Jablanović

Reputation: 44080

I guess an event overlaps if it begins before the range ends and ends after the range begins. :)

So:

{:conditions => ['? < ends_at AND ? > starts_at', range.first, range.last]}

Upvotes: 0

Tony Fontenot
Tony Fontenot

Reputation: 5101

There are four cases:

     Start    End
1.      |-----|
2.  |------|
3.  |-------------|
4.         |------|

Your named_scope only gets cases 1,2 and 4. So you just need need to add:

named_scope :in_range, lambda { |range|
  {:conditions => [
     '(starts_at BETWEEN ? AND ? OR ends_at BETWEEN ? AND ?) OR (starts_at <= ? AND ends_at >= ?)',
     range.first, range.last, range.first, range.last, range.first, range.last
   ]}
}

Upvotes: 24

Related Questions