使用Peewee CTE查询时出现‘Relation does not exist’错误求助

1. 先明确Peewee模型定义 #

先确认你的模型结构是否正确，以下是对应表关系的标准模型示例：

from peewee import *
import datetime

db = PostgresqlDatabase('your_database_name')

class BaseModel(Model):
    class Meta:
        database = db

class User(BaseModel):
    id = PrimaryKeyField()
    name = CharField()

class Event(BaseModel):
    id = PrimaryKeyField()
    name = CharField()

class Booking(BaseModel):
    id = PrimaryKeyField()
    event = ForeignKeyField(Event, backref='bookings')
    attendee = ForeignKeyField(User, backref='bookings')
    created_at = DateTimeField(default=datetime.datetime.now)  # 按创建时间排序取首位预订人

2. 正确实现CTE+窗口函数的查询逻辑 #

下面是能满足需求的Peewee代码，同时解决你遇到的两个报错：

from peewee import fn, Window

# 指定要查询的活动ID
target_event_id = 1

# 定义CTE：给预订记录加行号，同时统计总预订数
booking_cte = (Booking
               .select(
                   Booking.event_id,
                   Booking.attendee_id,
                   # 按创建时间升序排，给第一条记录打rn=1的标记
                   fn.row_number().over(
                       partition_by=Booking.event_id,
                       order_by=Booking.created_at.asc()
                   ).alias('rn'),
                   # 统计当前活动的总预订量
                   fn.count(Booking.id).over(partition_by=Booking.event_id).alias('total_bookings')
               )
               .where(Booking.event_id == target_event_id)
               .cte('booking_cte'))

# 主查询：关联用户表取首位预订人姓名，同时返回总预订数
result = (Event
          .select(
              Event.id,
              Event.name,
              booking_cte.c.total_bookings,
              User.name.alias('first_attendee')
          )
          .join(booking_cte, on=(Event.id == booking_cte.c.event_id))
          .join(User, on=(booking_cte.c.attendee_id == User.id))
          .where(booking_cte.c.rn == 1)
          # 兼容无预订的情况：返回总预订数0，首位姓名为NULL
          .union_all(
              Event.select(
                  Event.id,
                  Event.name,
                  fn.value(0).alias('total_bookings'),
                  fn.value(None).alias('first_attendee')
              ).where(
                  Event.id == target_event_id,
                  ~Event.bookings.exists()
              )
          )
          .first())

3. 报错排查与解决 #

（1）搞定"Relation does not exist"报错 #

• 必须用cte.c.列名的方式访问CTE的字段，不能直接用模型字段名，Peewee对CTE的列引用有固定语法。
• 检查模型外键是否配置正确，比如Booking.event是否关联到Event的主键，避免表/字段名不匹配。
• 确认数据库中实际存在对应表，模型的Meta类是否指定了正确的数据库连接。

（2）解决"rn is ambiguous"歧义问题 #

• 只要多个查询层级中出现相同的列别名（比如rn），必须给窗口函数的结果显式起别名，并且后续过滤/关联时要写全booking_cte.c.rn，不能只写rn，否则数据库无法识别你指向的是哪个查询中的列。
• 尽量避免在不同查询层级使用相同的列别名，必要时为不同逻辑的列取不同别名。

4. 生成的SQL说明 #

上述代码会生成符合需求的SQL，核心逻辑和你用纯SQL写的CTE+窗口函数完全一致：

WITH booking_cte AS (
    SELECT
        "t1"."event_id",
        "t1"."attendee_id",
        row_number() OVER (PARTITION BY "t1"."event_id" ORDER BY "t1"."created_at" ASC) AS rn,
        count("t1"."id") OVER (PARTITION BY "t1"."event_id") AS total_bookings
    FROM "booking" AS "t1"
    WHERE ("t1"."event_id" = 1)
)
SELECT
    "t2"."id",
    "t2"."name",
    "booking_cte"."total_bookings",
    "t3"."name" AS first_attendee
FROM "event" AS "t2"
INNER JOIN "booking_cte" ON ("t2"."id" = "booking_cte"."event_id")
INNER JOIN "user" AS "t3" ON ("booking_cte"."attendee_id" = "t3"."id")
WHERE ("booking_cte"."rn" = 1)
UNION ALL
SELECT
    "t2"."id",
    "t2"."name",
    0 AS total_bookings,
    NULL AS first_attendee
FROM "event" AS "t2"
WHERE (("t2"."id" = 1) AND NOT (EXISTS(SELECT 1 FROM "booking" AS "t1" WHERE ("t1"."event_id" = "t2"."id"))))
LIMIT 1

内容的提问来源于stack exchange，提问作者神話になれ