I want to query the count of bookings for a given event- if the event has bookings, I want to pull the name of the "first" person to book it.
The table looks something like: Event 1-0 or Many Booking, Booking.attendee is a 1:1 with User Table. In pure SQL I can easily do what I want by using Window Functions + CTE. Something like:
WITH booking AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY b.event_id ORDER BY b.created DESC) rn,
COUNT(*) OVER (PARTITION BY b.event_id) count
FROM
booking b JOIN "user" u on u.id = b.attendee_id
WHERE
b.status != 'cancelled'
)
SELECT e.*, a.vcount, a.first_name, a.last_name FROM event e LEFT JOIN attendee a ON a.event_id = e.id WHERE (e.seats > COALESCE(a.count, 0) and (a.rn = 1 or a.rn is null) and e.cancelled != true;
This gets everything I want. When I try to turn this into a CTE and use Peewee however, I get errors about: Relation does not exist.
Not exact code, but I'm doing something like this with some dynamic where clauses for filtering based on params.
cte = (
BookingModel.select(
BookingModel,
peewee.fn.ROW_NUMBER().over(partition_by=[BookingModel.event_id], order_by=[BookingModel.created.desc()]).alias("rn),
peewee.fn.COUNT(BookingModel.id).over(partition_by=[BookingModel.event_id]).alias("count),
UserModel.first_name,
UserModel.last_name
)
.join(
UserModel,
peewee.JOIN.LEFT_OUTER,
on(UserModel.id == BookingModel.attendee)
)
.where(BookingModel.status != "cancelled")
.cte("test")
query = (
EventModel.select(
EventModel,
UserModel,
cte.c.event_id,
cte.c.first_name,
cte.c.last_name,
cte.c.rn,
cte.c.count
)
.join(UserModel, on=(EventModel.host == UserModel.id))
.switch(EventModel)
.join(cte, peewee.JOIN.LEFT_OUTER, on=(EventModel.id == cte.c.event_id))
.where(where_clause)
.order_by(EventModel.start_time.asc(), EventModel.id.asc())
.limit(10)
.with_cte(cte)
After reading the docs twenty+ times, I can't figure out what isn't right about this. It looks like the samples... but the query will fail, because "relation "test" does not exist". I've played with "columns" being explicitly defined, but then that throws an error that "rn is ambiguous".
I'm stuck and not sure how I can get Peewee CTE to work.
EDIT: Generated query
WITH "attendee" AS (SELECT "t1"."id", "t1"."event_id", "t1"."attendee_id", "t1"."created", ROW_NUMBER() OVER (PARTITION BY "t1"."event_id" ORDER BY "t1"."created" DESC) AS "rn", COUNT("t1"."id") OVER (PARTITION BY "t1"."event_id") AS "count", "t2"."first_name", "t2"."last_name" FROM "booking" AS "t1" LEFT OUTER JOIN "user" AS "t2" ON ("t2"."id" = "t1"."attendee_id") WHERE ("t1"."status" != 'cancelled')) SELECT "t3"."id", "t3"."created", "t3"."updated", "t3"."course_id", "t3"."organizer_id", "t3"."sequence", "t3"."title", "t3"."desc", "t3"."seats", "t3"."location", "t3"."start_time", "t3"."end_time", "t3"."cancelled", "t4"."id", "t4"."created", "t4"."updated", "t4"."organization_id", "t4"."email", "t4"."first_name", "t4"."last_name", "attendee"."event_id", "attendee"."first_name", "attendee"."last_name", "attendee"."rn", "attendee"."count" FROM "event" AS "t3" INNER JOIN "user" AS "t4" ON ("t3"."organizer_id" = "t4"."id") LEFT OUTER JOIN "attendee" ON ("t3"."id" = "attendee"."event_id") WHERE (((("t3"."course_id" = '1d5687cb8d7b4211a461b353ecd4f5ec') AND (("attendee"."rn" = 1) OR ("attendee"."rn" IS NULL))) AND ("t3"."start_time" >= 0)) AND ("t3"."end_time" <= 3000)) ORDER BY "t3"."start_time" ASC, "t3"."id" ASC LIMIT 2
I did a ctrl+f for the string "test" and found it nowhere in the generated SQL, so something doesn't add up for me. Similarly, "attendee"."rn" seems well-specified, so I can't quite figure out where your issue is. Working backwards from the SQL, I produced the simplified models:
class User(db.Model):
name = TextField()
class Event(db.Model):
title = TextField()
organizer = ForeignKeyField(User)
class Booking(db.Model):
status = TextField()
event = ForeignKeyField(Event)
attendee = ForeignKeyField(User)
I populated some sample data and wrote the following simplified query:
cte = (Booking
.select(Booking.event,
User.name,
fn.ROW_NUMBER().over(
partition_by=[Booking.event],
order_by=[Booking.id.desc()]).alias('rn'),
fn.COUNT(Booking.id).over(
partition_by=[Booking.event]).alias('count'))
.join(User, JOIN.LEFT_OUTER)
.where(Booking.status != 'cancelled')
.cte('attendee'))
query = (Event
.select(Event, User.name.alias('organizer_name'),
cte.c.name,
cte.c.rn,
cte.c.count)
.join(User)
.join_from(Event, cte, JOIN.LEFT_OUTER, on=(Event.id == cte.c.event_id))
.where(cte.c.rn == 1)
.order_by(Event.id)
.with_cte(cte))
Iterating over the query appears correct:
for row in query:
print(
row.title, # Event title.
row.organizer.name, # Organizer's name.
row.attendee['name'], # Attendee name from cte.
row.attendee['rn'], # Row number from cte.
row.attendee['count']) # Count from cte.
Note: the results from the CTE are stored in a dict, as we don't have a well-defined model to use to reconstruct the join graph.
The above query produces the following SQL, which executes without issue:
WITH "attendee" AS (
SELECT "t1"."event_id", "t2"."name",
ROW_NUMBER() OVER (PARTITION BY "t1"."event_id" ORDER BY "t1"."id" DESC) AS "rn",
COUNT("t1"."id") OVER (PARTITION BY "t1"."event_id") AS "count"
FROM "booking" AS "t1"
LEFT OUTER JOIN "user" AS "t2" ON ("t1"."attendee_id" = "t2"."id")
WHERE ("t1"."status" != ?))
SELECT "t3"."id", "t3"."title", "t3"."organizer_id",
"t4"."name" AS "organizer_name",
"attendee"."name",
"attendee"."rn",
"attendee"."count"
FROM "event" AS "t3"
INNER JOIN "user" AS "t4"
ON ("t3"."organizer_id" = "t4"."id")
LEFT OUTER JOIN "attendee"
ON ("t3"."id" = "attendee"."event_id")
WHERE ("attendee"."rn" = ?)
ORDER BY "t3"."id"