CTE in SQL and Active Record

cte_as_active_record.rb

#
# CTE support in Active Record was added to Ruby on Rails 7.1
# https://blog.appsignal.com/2023/02/15/whats-new-in-rails-7-1.html#
#
# The example below produces an equivalent query to the SQL version below.
#
# Rails app: https://github.com/andyatkinson/rideshare
#
irb(main):395* Driver.with(drivers_recent_completed_trip: Driver.select('users.id AS driver_id').joins(trips: :trip_request).
irb(main):396*     where.not(trips: {completed_at: nil}).
irb(main):397*     where(trips: {completed_at: 1.hour.ago..}).
irb(main):398*     where(trip_requests: {end_location_id: 2})).
irb(main):399*            from("drivers_recent_completed_trip d").
irb(main):400*            select(
irb(main):401*              Arel.sql("users.id"),
irb(main):402*              Arel.sql("users.first_name || ' ' || users.last_name AS full_name"),
irb(main):403*              Arel.sql("AVG(trips.rating)"),
irb(main):404*            ).joins("JOIN users ON d.driver_id = users.id").
irb(main):405*            joins("JOIN trips ON trips.driver_id = d.driver_id").
irb(main):406*            where.not(trips: {rating: nil}).
irb(main):407*            where.not(trips: {completed_at: nil}).
irb(main):408*            group("users.id", "users.first_name || ' ' || users.last_name").
irb(main):409*            order(Arel.sql("AVG(trips.rating) DESC")).
irb(main):410*            limit(10).
irb(main):411>            map{ |d| [d.id, d.full_name, d.avg.to_f] }
  Driver Load (19.7ms)  WITH "drivers_recent_completed_trip" AS (SELECT users.id AS driver_id FROM "users" INNER JOIN "trips" ON "trips"."driver_id" = "users"."id" INNER JOIN "trip_requests" ON "trip_requests"."id" = "trips"."trip_request_id" WHERE "users"."type" = $1 AND "trips"."completed_at" IS NOT NULL AND "trips"."completed_at" >= $2 AND "trip_requests"."end_location_id" = $3) SELECT "users"."id", users.first_name || ' ' || users.last_name AS full_name, AVG(trips.rating) FROM drivers_recent_completed_trip d JOIN users ON d.driver_id = users.id JOIN trips ON trips.driver_id = d.driver_id WHERE "users"."type" = $4 AND "trips"."rating" IS NOT NULL AND "trips"."completed_at" IS NOT NULL GROUP BY "users"."id", users.first_name || ' ' || users.last_name ORDER BY AVG(trips.rating) DESC LIMIT $5  [["type", "Driver"], ["completed_at", "2024-04-26 13:13:43.828042"], ["end_location_id", 2], ["type", "Driver"], ["LIMIT", 10]]
=>
[[20100, "Calvin Cremin", 4.333333333333333],
 [20098, "Quentin Wilderman", 3.3333333333333335],
 [20005, "Gerri Olson", 3.0],
 [20062, "Tom Nitzsche", 2.8],
 [20032, "Nita Shanahan", 2.3333333333333335],
 [20004, "Pauline Adams", 2.0],
 [20066, "John Batz", 1.5],
 [20014, "Isidro Lehner", 1.5]]

cte_as_sql.sql

-- Row data estimates:
-- owner@localhost:5432 rideshare_development# SELECT relname AS tablename, reltuples::numeric AS estimate                                                              FROM pg_class WHERE relname IN ('users', 'trips', 'trip_requests');                                                                                                     tablename   | estimate
-- ---------------+----------
--  trip_requests |  6001010
--  trips         |  5001000
--  users         | 75020200
-- (3 rows)


WITH drivers_recent_completed_trip AS (
    SELECT
        users.id AS driver_id
    FROM
        users
    JOIN
        trips ON trips.driver_id = users.id
    JOIN
        trip_requests ON trip_requests.id = trips.trip_request_id
    WHERE
        users.type = 'Driver'
        AND trips.completed_at IS NOT NULL
        AND trips.completed_at >= (NOW() - interval '1 hour')
        AND trip_requests.end_location_id = 2
)
SELECT
    users.id AS driver_id,
    first_name || ' ' || last_name AS full_name,
    AVG(trips.rating) AS avg_rating
FROM
    drivers_recent_completed_trip d
JOIN
    users ON d.driver_id = users.id
JOIN
    trips ON trips.driver_id = d.driver_id
WHERE
    trips.rating IS NOT NULL
    AND trips.completed_at IS NOT NULL
    AND type = 'Driver'
GROUP BY
    1, 2
ORDER BY
    3 DESC
LIMIT 10;
driver_id |     full_name     |     avg_rating
-----------+-------------------+--------------------
     20100 | Calvin Cremin     | 4.3333333333333333
     20098 | Quentin Wilderman | 3.3333333333333333
     20005 | Gerri Olson       | 3.0000000000000000
     20062 | Tom Nitzsche      | 2.8000000000000000
     20032 | Nita Shanahan     | 2.3333333333333333
     20004 | Pauline Adams     | 2.0000000000000000
     20066 | John Batz         | 1.5000000000000000
     20014 | Isidro Lehner     | 1.5000000000000000
(8 rows)

Time: 20.926 ms