postgresql Peewee：使用CTE查询时关系不存在

h7appiyu 于 2023-02-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(150)

我想查询给定事件的预订计数-如果事件有预订，我想提取预订它的“第一个”人的姓名。
该表类似于：事件1-0或许多预订，预订。与会者是一个1：1与用户表。在纯SQL中，我可以很容易地做我想用窗口函数+ CTE。类似于：

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;

这得到了我想要的一切。当我尝试将其转换为CTE并使用Peewee时，我得到了以下错误：关系不存在。

不是精确的代码，但是我正在使用一些动态where子句来基于参数进行过滤。

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)

在阅读了20多遍文档后，我无法找出其中的错误。它看起来像示例...但查询将失败，因为“关系“测试”不存在”。我曾尝试过显式定义“列”，但随后会抛出“rn不明确”的错误。
我被卡住了，不知道怎么才能让皮威CTE工作。
编辑：生成的质询

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

postgresql

来源：https://stackoverflow.com/questions/75505944/peewee-relation-does-not-exist-when-querying-with-cte

1条答案

按热度按时间

pftdvrlh1#

我对字符串“test”执行了ctrl+f，但在生成的SQL中找不到它，所以有些事情对我来说不太对劲。类似地，“attendee”.“rn”似乎指定得很好，所以我不能很好地找出问题所在。从SQL反向工作，我生成了简化的模型：

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)

我填充了一些示例数据，并编写了以下简化查询：

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

遍历查询看起来是正确的：

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.

注意：CTE的结果存储在一个dict中，因为我们没有一个定义良好的模型来重建连接图。
上面的查询生成以下SQL，执行时不会出现问题：

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"

赞(0）回复(0）举报 2023-02-22

我来回答

postgresql Peewee：使用CTE查询时关系不存在

1条答案

相关问题

热门标签

最新问答