Finding records that overlap a range in Rails

Question

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?

Answer 1

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]}

Answer 2

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
   ]}
}