SQLAlchemy多对多关系：查询Bar集合完全匹配的Foo对象

我有如下Model类及关联表：

class Foo(Model):
   ...
   bars = db.relationship("Bar", secondary=foo_bar, backref="foos")
   
class Bar(Model):
   ...

foo_bar = db.Table("foo_bar",
  Column("foo_id", ...),
  Column("bar_id", ...)
)

需要查询仅关联指定Bar对象列表、无其他关联的Foo对象——比如给定bars = [bar1, bar2, bar3]，只返回恰好关联这三个Bar（不多也不少）的Foo对象：

bars = [bar1, bar2, bar3] # 此前查询得到的Bar对象列表
matching_foos = Foo.query.filter(???).all() # 仅返回仅关联bar1、bar2、bar3的Foo对象

我尝试过以下方法，但均报错无效：

Foo.query.filter(
   Foo.bars.contains(bar),
   Foo.bars.contained_by(bar),
)

Foo.query.filter_by(
   Foo.bars = bars
)

正确解决方案 #

要实现完全匹配，需要同时满足两个核心条件：

1. Foo关联的所有Bar都属于指定列表（没有额外的Bar）
2. Foo关联的Bar数量和指定列表的长度一致（确保没有遗漏指定的Bar）

方法一：使用集合过滤与数量校验 #

from sqlalchemy import func

# 先提取目标Bar的ID列表
bar_ids = [bar.id for bar in bars]

matching_foos = Foo.query.filter(
    # 排除关联了列表外Bar的Foo
    ~Foo.bars.any(Bar.id.notin_(bar_ids)),
    # 确保关联的Bar数量等于目标列表长度
    func.count(Foo.bars).over(partition_by=Foo.id) == len(bar_ids)
).all()

方法二：通过关联表分组统计 #

from sqlalchemy import func

bar_ids = [bar.id for bar in bars]

matching_foos = Foo.query.join(foo_bar).filter(
    foo_bar.c.bar_id.in_(bar_ids)
).group_by(Foo.id).having(
    # 分组后统计的关联Bar数量等于目标数量
    func.count(foo_bar.c.bar_id) == len(bar_ids),
    # 再次确认该Foo没有关联其他Bar
    func.count(Foo.bars) == len(bar_ids)
).all()

为什么之前的方法无效 #

• contains/contained_by：这两个方法仅检查集合的包含关系，无法保证数量完全匹配。比如contained_by只能确保Foo的Bar都在列表里，但无法确认是否包含了所有指定的Bar；且你传入的参数有误（contains需要单个元素，contained_by需要可迭代对象）。
• filter_by：无法直接将关系属性与列表做相等比较，因为Foo.bars是一个集合对象，不是简单的列表或值。

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