如何用SQLAlchemy获取关联用户/订单少于3个的Product记录

你的核心问题在于多对多关联下的查询逻辑错误，导致统计结果失真、过滤条件失效，具体问题点和修复方案如下：

原查询的核心错误 #

1. 多对多关联引用错误：Product与User、Order是多对多关系，不存在直接的Product.user_id外键字段，你错误地用了一对一/一对多的关联方式来引用。
2. 子查询未关联外层Product：你的子查询stmt和stmt1没有和外层的Product记录绑定，统计的是全局的User/Order总数，而非单个Product对应的关联数量，导致过滤条件完全失效。
3. 多表Join产生笛卡尔积：同时外层Join订单和用户表会生成笛卡尔积（比如一个Product有2个用户+2个订单，会生成4行重复记录），直接统计会导致数量虚高，最终过滤结果错误。

正确解决方案 #

方案一：预统计子查询（推荐，逻辑清晰） #

先分别统计每个Product的关联用户数、订单数，再关联主表过滤：

from sqlalchemy import func, select

# 统计每个Product的关联用户数（去重避免重复计数）
user_count_subq = (
    select(
        Product.id,
        func.count(User.id.distinct()).label("user_count")
    )
    .outerjoin(Product.users)
    .group_by(Product.id)
    .subquery()
)

# 统计每个Product的关联订单数
order_count_subq = (
    select(
        Product.id,
        func.count(Order.id.distinct()).label("order_count")
    )
    .outerjoin(Product.orders)
    .group_by(Product.id)
    .subquery()
)

# 主查询：关联统计结果并过滤
query = (
    select(Product)
    .join(user_count_subq, Product.id == user_count_subq.c.id)
    .join(order_count_subq, Product.id == order_count_subq.c.id)
    .filter(
        user_count_subq.c.user_count < 3,
        order_count_subq.c.order_count < 3
    )
)

# 执行查询
result = session.execute(query).scalars().all()

方案二：Group By + Having子句（简洁版） #

通过分组后直接用Having过滤，注意必须对关联的User/Order ID去重：

query = (
    select(Product)
    .outerjoin(Product.users)
    .outerjoin(Product.orders)
    .group_by(Product.id)
    .having(
        func.count(User.id.distinct()) < 3,
        func.count(Order.id.distinct()) < 3
    )
)

result = session.execute(query).scalars().all()

关键说明 #

• 多对多关联下，必须通过Product.users、Product.orders这类关联属性来关联中间表，不能直接引用不存在的外键字段。
• 统计关联数量时必须用distinct()，避免多表Join产生的笛卡尔积导致重复计数。
• 子查询必须和外层的Product ID绑定，才能实现“每个Product单独统计”的逻辑。

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