ActiveRecord-如何在“has_many”子關系的MIN和MAX上創建范圍？

我的關系如下：

class Trip < ApplicationRecord
  has_many :trip_destinations, dependent: :destroy
end

class TripDestination < ApplicationRecord
  belongs_to :trip
  
  validates :start_date, presence: true
  validates :end_date, presence: true
end

我想撰寫一個查詢，回傳之前但之后的所有行程MIN(trip_destinations.start_date)Date.todayMAX(trip_destinations.end_date)Date.today；也就是說，Trip仍然有TripDestinations 在今天之前開始并在今天之后結束。

目前除錯：

目前，我所擁有的是：

    Trip
      .joins(:trip_destinations)
      .where("
        trip_destinations.start_date = (
          SELECT MIN(trip_destinations.start_date) FROM trip_destinations
             WHERE trips.id = trip_destinations.trip_id
        )
        AND trip_destinations.start_date <= ?", today
      )
      .where("
        trip_destinations.end_date = (
          SELECT MAX(trip_destinations.end_date) FROM trip_destinations
             WHERE trips.id = trip_destinations.trip_id
        )
        AND trip_destinations.end_date > ?", today
      )
      .group("trips.id")

輸出的 SQL：

      SELECT 1 AS one FROM "trips" INNER JOIN "trip_destinations" ON "trip_destinations"."trip_id" = "trips"."id" WHERE "trips"."published" = $1 AND (
        trip_destinations.start_date = (
          SELECT MIN(trip_destinations.start_date) FROM trip_destinations
             WHERE trips.id = trip_destinations.trip_id
        )
        AND trip_destinations.start_date <= '2022-11-08') AND (
        trip_destinations.end_date = (
          SELECT MAX(trip_destinations.end_date) FROM trip_destinations
             WHERE trips.id = trip_destinations.trip_id
        )
        AND trip_destinations.end_date > '2022-11-08') GROUP BY "trips"."id" LIMIT $2  [["published", true], ["LIMIT", 1]]

它回傳一個空 set []，即使我已經手動驗證了Trip這種性質的存在。

更重要的是，我可以將 SQL 查詢分解為組件WHERE子句，它們都獨立作業。IE

    Trip
      .joins(:trip_destinations)
      .where("
        trip_destinations.start_date = (
          SELECT MIN(trip_destinations.start_date) FROM trip_destinations
             WHERE trips.id = trip_destinations.trip_id
        )
        AND trip_destinations.start_date <= ?", today
      )

返程，還有

    Trip
      .joins(:trip_destinations)
      .where("
        trip_destinations.end_date = (
          SELECT MAX(trip_destinations.end_date) FROM trip_destinations
             WHERE trips.id = trip_destinations.trip_id
        )
        AND trip_destinations.end_date > ?", today
      )

回傳行程。但是，當我將它們鏈接WHERE在一起時，出于某種原因ActiveRecord或Postgres不喜歡它。

也可能值得注意的是，我不需要包含start_date <= ?這個來打破。

    Trip
      .joins(:trip_destinations)
      .where("
        trip_destinations.start_date = (
          SELECT MIN(trip_destinations.start_date) FROM trip_destinations
             WHERE trips.id = trip_destinations.trip_id
        )
      )
      .where("
        trip_destinations.end_date = (
          SELECT MAX(trip_destinations.end_date) FROM trip_destinations
             WHERE trips.id = trip_destinations.trip_id
        )
      )
      .group("trips.id")

也回傳一個空集。

期望的結果

一個查詢（子查詢很好），它回傳所有Trip至少有一個TripDestination帶有 astart_date <= Date.today和end_date > Date.today

uj5u.com熱心網友回復：

您是否嘗試過像這樣將兩個 where 子句合并為一個？

Trip.joins(:trip_destinations)
    .where(
      "(
        trip_destinations.start_date = (
          SELECT MIN(trip_destinations.start_date) FROM trip_destinations WHERE trips.id = trip_destinations.trip_id
        )
        AND trip_destinations.start_date <= ?
      ) AND
      (
        trip_destinations.end_date = (
          SELECT MAX(trip_destinations.end_date) FROM trip_destinations WHERE trips.id = trip_destinations.trip_id
        )
        AND trip_destinations.end_date > ?
      )", today, today
    )

uj5u.com熱心網友回復：

正如您所說，期望的結果是：一個查詢（子查詢很好），它回傳所有 Trip 記錄，這些記錄至少有一個 TripDestination 且 start_date <= Date.today 和 end_date > Date.today

可以試試這個范圍嗎？

  scope :strict, -> {
      distinct
      .joins(:trip_destinations)
      .where(trip_destinations: {
        start_date: ..DateTime.now.beginning_of_day
      })
      .where('trip_destinations.end_date > ?', DateTime.now.end_of_day )
  }

uj5u.com熱心網友回復：

雖然我仍然不確定為什么以下內容不能解決問題

Trip
  .joins(:trip_destinations)
  .where(trip_destinations: {start_date: ..Date.today, end_date: Date.today...})

這將滿足宣告的實際愿望：“回傳所有Trip至少有一個TripDestination帶有 astart_date <= Date.today和end_date > Date.today”的記錄

但是，如果您的真正愿望是“回傳第一個小于或等于今天且最后一個大于今天的所有Trip記錄”，TripDestinationstart_dateend_date那么您可以改為使用具有聚合函式的 HAVING 子句，如下所示：

 Trip
   .joins(:trip_destinations)
   .group(:id) 
   .having( 
      TripDestination.arel_table[:start_date].minimum.lteq(Date.today).and(
       TripDestination.arel_table[:end_date].maximum.gt(Date.today))
   )

生成的 SQL：

SELECT 
  trips.* 
FROM 
  trips
  INNER JOIN trip_destinations ON trip_destinations.trip_id = trips.id 
GROUP BY 
  trips.id 
HAVING 
  MIN(trip_destinations.start_date) <= '2022-11-08' AND 
  MAX(trip_destinations.end_date) > '2022-11-08' 

此解決方案假定 PostgreSQL 或 MySQL（禁用 ONLY_FULL_GROUP_BY）。如果不是這種情況，那么您可以選擇

Trip.where(id: 
  Trip 
    .select(:id)
    .joins(:trip_destinations)
    .group(:id) 
    .having( 
      TripDestination.arel_table[:start_date].minimum.lteq(Date.today).and(
       TripDestination.arel_table[:end_date].maximum.gt(Date.today))
   )
)

生成的 SQL：

SELECT 
  trips.* 
FROM 
  trips 
WHERE 
  trips.id IN ( 
    SELECT 
      trips.id 
    FROM 
      trips
      INNER JOIN trip_destinations ON trip_destinations.trip_id = trips.id 
    GROUP BY 
      trips.id 
    HAVING 
      MIN(trip_destinations.start_date) <= '2022-11-08' AND 
      MAX(trip_destinations.end_date) > '2022-11-08' 
)

標籤：sql轨道上的红宝石加入活动记录

上一篇：一張表中的SQL多重選擇陳述句

下一篇：如何計算日期時間間隔