SQLAlchemy一对多关联查询：筛选无指定事件的Product

问题说明 #

• Product和Event是一对多关系：一个Product对应多个Event，每个Event只属于一个Product
• Event的name字段可选值有：created-invoice、approved-invoice、item-pickup、item-delivered、item-cancelled
• 目标：找出所有关联Event中完全不存在item-pickup、item-delivered、item-cancelled这三类事件的Product

原代码问题分析 #

你之前的写法逻辑有误：

param_list = ['item-pickup', 'item-delivered', 'item-cancelled']
 
stmt = (select(Product.id, Product.consignment_id, Event.name)
    .join(Product.events)
    .filter(Event.name.not_in(param_list))
    .group_by(Product.id)
    .order_by(Event.name.desc()))

这段代码只是过滤掉了名称在param_list里的Event行，但如果某个Product同时包含符合过滤条件的Event（比如approved-invoice）和不符合的Event（比如item-cancelled），该Product依然会被查询出来——因为内连接会保留存在符合过滤条件的Event的Product记录，分组后自然会留下这个Product。

解决方案 #

下面提供三种可行的实现方式：

方式1：子查询排除法 #

先找出所有关联了指定事件的Product ID，再排除这些ID，剩下的就是符合要求的Product：

from sqlalchemy import select

param_list = ['item-pickup', 'item-delivered', 'item-cancelled']

# 子查询：获取所有存在指定事件的Product ID
invalid_product_ids = select(Event.product_id).where(Event.name.in_(param_list)).distinct()

# 主查询：筛选不在无效ID列表中的Product
stmt = (
    select(Product.id, Product.consignment_id)
    .where(Product.id.not_in(invalid_product_ids))
    .order_by(Product.id)
)

方式2：LEFT JOIN + IS NULL #

通过左连接关联指定事件，筛选出没有匹配结果的Product（即无指定事件的Product）：

from sqlalchemy import select, and_

param_list = ['item-pickup', 'item-delivered', 'item-cancelled']

stmt = (
    select(Product.id, Product.consignment_id)
    .outerjoin(Event, and_(Product.id == Event.product_id, Event.name.in_(param_list)))
    .where(Event.id.is_(None))
    .group_by(Product.id)
    .order_by(Product.id)
)

方式3：HAVING子句统计法 #

分组后统计符合指定事件的数量，筛选数量为0的Product（适合需要同时关联Event的场景）：

from sqlalchemy import select, func, case

param_list = ['item-pickup', 'item-delivered', 'item-cancelled']

stmt = (
    select(Product.id, Product.consignment_id)
    .outerjoin(Event)  # 左连接避免漏掉无任何Event的Product
    .group_by(Product.id)
    .having(func.count(case((Event.name.in_(param_list), 1))).is_(0))
    .order_by(Product.id)
)

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