Rubyonrails 在has_many关系中由两个条件 Select

似乎你只是缺少了一个连接和后续的过滤条件:

NOT EXISTS (SELECT 1 FROM bookings WHERE arrival >= ?)

为了确保此过滤器基于特定的人，您需要将其更改为:

NOT EXISTS (
  SELECT 1 
  FROM bookings 
  INNER JOIN stays ON stays.booking_id = bookings.id 
  WHERE arrival >= ? AND stays.person_id = people.id)

我们应该能够在ActiveRecord/Arel中转换这个查询，如下所示:

Person.joins(:bookings)
  .where(bookings: {departure: ...1.month.ago})
  .where.not(
      Stay.select(1)
        .joins(:bookings)
        .where(bookings: {arrival: Time.zone.today..})
        .where(Stay.arel_table[:person_id].eq(Person.arel_table[:id]))
        .arel.exists
  )

这将导致以下查询

SELECT 
  people.*
FROM 
  people 
  INNER JOIN stays ON stays.person_id = people.id
  INNER JOIN bookings ON bookings.id = stays.booking_id
WHERE 
  bookings.departure < '2024-02-26' 
AND 
  NOT (
    EXISTS (
      SELECT 1 
      FROM 
        stays
        INNER JOIN bookings ON bookings.id = stays.booking_id 
      WHERE 
        stays.person_id = people.id 
        AND bookings.arrival >= '03-26-2024'))

或者，你可以使用两个单独的IN子句.代码稍微干净一些，但可能会对性能产生影响.

Person
  .where(id: 
    Stays.joins(:bookings).select(:person_id).where(bookings: {departure: ...1.month.ago}))
  .where.not(id: 
    Stays.joins(:bookings).select(:person_id).where(bookings:{arrival: Time.zone.today..}))

SQL:好的，好的.

SELECT 
  people.*
FROM 
  people 
WHERE 
  people.id IN ( 
    SELECT 
      stays.person_id
    FROM 
        stays
        INNER JOIN bookings ON bookings.id = stays.booking_id 
      WHERE 
        bookings.departure < '2024-02-26') 
AND 
  people.id NOT IN ( 
    SELECT 
      stays.person_id
    FROM 
        stays
        INNER JOIN bookings ON bookings.id = stays.booking_id 
      WHERE 
        bookings.arrival >= '03-26-2024')