ActiveRecord - how to create a scope on the MIN and MAX of a `has

I have a relationship as follows:

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

I want to write a query that returns all trips where the MIN(trip_destinations.start_date) is before Date.today but the MAX(trip_destinations.end_date) is after Date.today; that is, Trip still has TripDestinations that start before today AND end after today.

Debugging so far:

Currently, what I have is:

    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")

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

which returns an empty set [], even though I've manually verified that a Trip exists of this nature.

What's more, I can break down the SQL query in to the component WHERE clauses and they both work independently. i.e.

    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
      )

Returns the trip, and also

    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
      )

returns the trip. However, when I chain those WHERE together, for some reason ActiveRecord or Postgres don't like it.

It's probably worth noting too, that I don't need to include the start_date <= ? for this to break.

    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")

also returns an empty set.

Desired outcome

A single query (subqueries are fine) which returns all Trip records that have at least one TripDestination with a start_date <= Date.today and end_date > Date.today

CodePudding user response：

Have you tried merging the two where clauses into one like so ?

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
    )

CodePudding user response：

As you stated that the desired outcome is : A single query (subqueries are fine) which returns all Trip records that have at least one TripDestination with a start_date <= Date.today and end_date > Date.today

Can try this scope ?

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