解决SQLAlchemy查询未完成全部评分用户的笛卡尔积警告问题

问题场景 #

数据库里有固定数量的录音，每个用户必须给每段录音评分，单条评分存在ratings表中。现有4个录音（ID 1~4）和4个用户（ID 1~4），已有的评分数据如下：

{user_id: 1, recording_id: 1} {user_id: 1, recording_id: 2} {user_id: 1, recording_id: 3} {user_id: 1, recording_id: 4}
{user_id: 2, recording_id: 1} {user_id: 2, recording_id: 3} {user_id: 2, recording_id: 4}
{user_id: 4, recording_id: 1} {user_id: 4, recording_id: 2} {user_id: 4, recording_id: 3} {user_id: 4, recording_id: 4}

需要筛选出未完成全部录音评分的用户——期望返回用户ID 2（未评录音2）和3（未评任何录音）。

错误尝试及问题 #

最初写的查询代码如下：

results = session.query(User).filter(
        ~Recording.id.in_(
            select(Rating.recording_id).where(Rating.user_id == User.id)
            )
        ).all()

虽然能得到结果，但触发了笛卡尔积警告：

SAWarning: SELECT statement has a cartesian product between FROM element(s) "recordings" and FROM element "users".  Apply join condition(s) between each element to resolve.
  ).all()

尝试手动关联表后：

session.query(User).join(Rating).join(Recording).filter(...)

又出现报错：

Select statement returned no FROM clauses due to auto-correlation; specify correlate() to control correlation manually

解决方案 #

核心问题是原查询直接引用未关联的Recording表导致隐式笛卡尔积，下面提供两种可行的解决方式：

方案1：统计评分数量对比 #

先统计总录音数，再对比每个用户已评分的录音数量，筛选出数量不匹配的用户：

# 先获取总录音数
total_recordings = session.query(func.count(Recording.id)).scalar()

# 查询未完成全部评分的用户
results = session.query(User).filter(
    session.query(func.count(Rating.id))
    .where(Rating.user_id == User.id)
    .correlate(User)  # 明确关联外层User表，解决自动关联问题
    .scalar_subquery() != total_recordings
).all()

方案2：左连接+分组统计 #

通过左连接关联用户和评分表，分组后统计每个用户的评分数量，再和总录音数对比：

total_recordings = session.query(func.count(Recording.id)).scalar()

results = session.query(User).outerjoin(
    Rating, User.id == Rating.user_id
).group_by(User.id).having(
    func.count(Rating.recording_id.distinct()) < total_recordings
).all()

这种方式更直观，还能自然覆盖用户无任何评分的场景（比如用户3）。

关键注意点 #

• 不要在filter中直接引用未关联的表，否则会触发笛卡尔积。
• 使用correlate()可以明确子查询关联的外层表，解决自动关联报错。
• 左连接分组的方式兼容性更强，逻辑也更容易理解。

内容的提问来源于stack exchange，提问作者Gautzilla