在 Rails 中查找与范围重叠的记录 [英] Finding records that overlap a range in Rails

问题描述

所以，我有一个 Event 模型，它有一个 starts_at 和一个 ends_at 列，我想找到发生在日期范围.

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.

我想出了这个 named_scope(range 通常是一个月):

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

按预期工作.

但是如果事件在之前的一个月开始并且在该范围之后的一个月结束，则它不会显示.有什么办法可以以适当的方式找到这些事件吗?

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?

推荐答案

有四种情况:

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

您的named_scope 仅获取情况1,2 和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
   ]}
}

这篇关于在 Rails 中查找与范围重叠的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！