基于PostgreSQL与ActiveRecord，查询重叠分配的更优Rails实现

数据库 Schema #

User(:id)
Event(:id, :start_date, :end_date,:duration, :recurrence_pattern)
EventAssignment(:user_id, :event_id, :date)

• recurrence_pattern：rrule格式字符串
• date：YYYY-MM-DD格式的日期字段
• start_date 和 end_date：YYYY-MM-DDTHH:MM:SS格式的时间戳字段

需求 #

查找所有存在至少一项分配与指定时间范围（from 和 to 两个时间戳）重叠的用户。

现有实现代码 #

用户已编写基于PostgreSQL和Arel的查询代码：

assignment_subquery = EventAssignment.joins(:event).where(
    '"user_id" = users.id AND
    (?) <= "event_assignments".date + (to_char("events".start_date, \'HH24:MI\'))::time + make_interval(mins => "events".duration) AND
    (event_assignments.date) + (to_char(events.start_date, \'HH24:MI\'))::time <= (?)',
from, to).arel.exists

User.where(assignment_subquery)

编辑补充的PostgreSQL写法：

assignment_subquery = EventAssignment.joins(:event).where(
'"user_id" = users.id AND
    ("event_assignments".date + (to_char("events".start_date, \'HH24:MI\'))::time + make_interval(mins => "events".duration),
    (event_assignments.date) + (to_char(events.start_date, \'HH24:MI\'))::time) 
 OVERLAPS ((?), (?))'
, from, to).arel.exists

User.where(assignment_subquery)

现有代码可正常运行，请问是否存在更符合Rails风格的实现方式？

可以通过以下几种方式优化代码，减少硬编码SQL字符串，贴合Rails ActiveRecord的最佳实践，提升可读性与可维护性：

1. 用Arel构建时间计算逻辑 #

利用Arel节点替代硬写的SQL片段，让查询逻辑更贴合Rails的ORM风格：

# 获取各表的Arel节点
users_table = User.arel_table
ea_table = EventAssignment.arel_table
events_table = Event.arel_table

# 计算事件实际开始时间：分配日期 + 事件起始时间的时分部分
event_start = ea_table[:date]
              .cast_to_time
              .add(events_table[:start_date].extract(:hour).multiply(3600)
                   .add(events_table[:start_date].extract(:minute).multiply(60))
                   .cast_to_interval)

# 计算事件实际结束时间：开始时间 + 持续时长（分钟）
event_end = event_start.add(
  Arel::Nodes::NamedFunction.new('make_interval', [
    Arel::Nodes::SqlLiteral.new("mins => #{events_table[:duration].to_sql}")
  ])
)

# 构建时间重叠条件
overlap_condition = Arel::Nodes::Overlap.new(
  Arel::Nodes::Grouping.new([event_end, event_start]),
  Arel::Nodes::Grouping.new([Arel.sql('?'), Arel.sql('?')])
)

# 构建子查询并执行用户查询
assignment_subquery = EventAssignment.joins(:event)
                                     .where(ea_table[:user_id].eq(users_table[:id]))
                                     .where(overlap_condition, from, to)
                                     .arel.exists

User.where(assignment_subquery)

2. 利用模型关联简化查询 #

先确保模型间配置好正确的关联关系：

# app/models/user.rb
class User < ApplicationRecord
  has_many :event_assignments
  has_many :events, through: :event_assignments
end

# app/models/event_assignment.rb
class EventAssignment < ApplicationRecord
  belongs_to :user
  belongs_to :event
end

# app/models/event.rb
class Event < ApplicationRecord
  has_many :event_assignments
  has_many :users, through: :event_assignments
end

之后可以用更简洁的链式查询实现需求：

User.joins(event_assignments: :event)
    .where(
      # 事件开始时间 <= 查询结束时间
      ea_table[:date].cast_to_time.add(events_table[:start_date].extract(:hour).multiply(3600)
                                       .add(events_table[:start_date].extract(:minute).multiply(60))
                                       .cast_to_interval).lteq(to),
      # 事件结束时间 >= 查询开始时间
      ea_table[:date].cast_to_time.add(events_table[:start_date].extract(:hour).multiply(3600)
                                       .add(events_table[:start_date].extract(:minute).multiply(60))
                                       .cast_to_interval)
      .add(Arel::Nodes::NamedFunction.new('make_interval', [
             Arel::Nodes::SqlLiteral.new("mins => #{events_table[:duration].to_sql}")
           ])).gteq(from)
    )
    .distinct

或者结合exists写法：

User.where(
  EventAssignment.joins(:event)
                 .where('event_assignments.user_id = users.id')
                 .where(
                   '(event_assignments.date + (to_char(events.start_date, \'HH24:MI\'))::time) <= ?', to,
                   '(event_assignments.date + (to_char(events.start_date, \'HH24:MI\'))::time + make_interval(mins => events.duration)) >= ?', from
                 )
                 .exists
)

3. 封装逻辑为模型作用域 #

把时间重叠的判断逻辑封装到Event模型的作用域中，提升代码复用性：

# app/models/event.rb
class Event < ApplicationRecord
  scope :overlaps_with_range, ->(from, to) {
    arel_table = self.arel_table
    ea_table = EventAssignment.arel_table

    event_start = ea_table[:date].cast_to_time.add(
      arel_table[:start_date].extract(:hour).multiply(3600)
        .add(arel_table[:start_date].extract(:minute).multiply(60))
        .cast_to_interval
    )
    event_end = event_start.add(
      Arel::Nodes::NamedFunction.new('make_interval', [
        Arel::Nodes::SqlLiteral.new("mins => #{arel_table[:duration].to_sql}")
      ])
    )

    where(Arel::Nodes::Overlap.new(
      Arel::Nodes::Grouping.new([event_end, event_start]),
      Arel::Nodes::Grouping.new([Arel.sql('?'), Arel.sql('?')])
    ), from, to)
  }
end

之后查询时直接调用作用域即可：

User.where(
  EventAssignment.joins(:event)
                 .where('event_assignments.user_id = users.id')
                 .merge(Event.overlaps_with_range(from, to))
                 .exists
)

这些实现方式都尽量避免了硬写完整SQL字符串，利用Arel和ActiveRecord的关联、作用域特性，让代码更符合Rails风格，同时保持逻辑清晰。

内容的提问来源于stack exchange，提问作者Ismail Yilmaz