根据关联模型的属性的最大值返回所有实例

问：

意识到标题根本不好。从本质上讲，我有一个与 有关系的模型。我想返回所有没有任何时间段的 TaskReferral 实例，这些实例的日期超过给定日期TaskReferralhas_manyTimeSlotstarted_at

class MissionReferral < TaskReferral
  has_many :time_slots
end

class TimeSlot
  belongs_to :booking, polymorphic: true
end

我在'mission_referral.rb中尝试这样的事情：

scope :with_no_timeslots_after, lambda { |date|
    from(
      <<~SQL
        (
          SELECT *
          FROM task_referrals tr
          JOIN (
            SELECT booking_id, booking_type, max(started_at) as started_at
            FROM time_slots AS ts
            GROUP by booking_id, booking_type
          ) as latest_time_slot
          ON tr.id =  latest_time_slot.booking_id
          AND latest_time_slot.booking_type = 'TaskReferral'
          WHERE started_at < #{date.to_date}
        )
      SQL
    )
  }

但是当我跑步时，我得到：MissionReferral.with_no_timeslots_after(1.day.from_now)

MissionReferral Load (0.9ms)  SELECT "task_referrals".* FROM (
  SELECT *                                                                                                           
  FROM task_referrals tr                                                                                             
  JOIN (                                                                                                             
    SELECT booking_id, booking_type, max(started_at) as started_at                                                   
    FROM time_slots AS ts                                                                                            
    GROUP by booking_id, booking_type                                                                                
  ) as latest_time_slot                                                                                              
  ON tr.id =  latest_time_slot.booking_id                                                                            
  AND latest_time_slot.booking_type = 'TaskReferral'                                                                 
  WHERE started_at < 2023-09-16                                                                                      
)                                                                                                                    
 WHERE "task_referrals"."type" = $1  [["type", "MissionReferral"]]                                                   
(Object doesn't support #inspect)     

或者如果我运行，我会得到：MissionReferral.with_no_timeslots_after(1.day.from_now).count

MissionReferral Count (1.1ms)  SELECT COUNT(*) FROM (
  SELECT *                                                                                                           
  FROM task_referrals tr                                                                                             
  JOIN (                                                                                                             
    SELECT booking_id, booking_type, max(started_at) as started_at                                                   
    FROM time_slots AS ts                                                                                            
    GROUP by booking_id, booking_type                                                                                
  ) as latest_time_slot                                                                                              
  ON tr.id =  latest_time_slot.booking_id                                                                            
  AND latest_time_slot.booking_type = 'TaskReferral'                                                                 
  WHERE started_at < 2023-09-16                                                                                      
)                                                                                                                    
 WHERE "task_referrals"."type" = $1  [["type", "MissionReferral"]]                                                   
/home/dave-gg/.rbenv/versions/3.1.2/lib/ruby/gems/3.1.0/gems/activerecord-7.0.4.2/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params': PG::SyntaxError: ERROR:  subquery in FROM must have an alias (ActiveRecord::StatementInvalid)
LINE 1: SELECT COUNT(*) FROM (
                             ^
HINT:  For example, FROM (SELECT ...) [AS] foo.
/home/dave-gg/.rbenv/versions/3.1.2/lib/ruby/gems/3.1.0/gems/activerecord-7.0.4.2/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params': ERROR:  subquery in FROM must have an alias (PG::SyntaxError)
LINE 1: SELECT COUNT(*) FROM (
                             ^
HINT:  For example, FROM (SELECT ...) [AS] foo.

奇怪的是，我可以让查询在连接到数据库的 SQL 编辑器中运行，所以不要以为我离得太远了。任何帮助将不胜感激。