SQLAlchemy - 通过子对象查询父对象，过滤连接以匹配列表中的每个子对象

Question

我正在尝试查询一个拥有所有请求技能的人员。不太确定如何动态构建这个查询。

下面是ORM对象。人员和技能之间是多对多的关系，这里没有包含连接表：

Person
name=John, skills=[Skill(id=1)] # John knows Python
name=Eve, skills=[Skill(id=1), Skill(id=2)] # Eve knows Python and SQLAlchemy

Skill
id=1, name=Python
id=2, name=SQLAlchemy
id=3, name=Social skills

在搜索页面，用户可以选择一个或多个技能，然后根据这些技能生成人员的搜索结果。比如说，用户想找一个会Python和SQLAlchemy的人。应用程序会从表单提交中获取两个ID的列表，[1, 2]。我写了以下查询，基本上可以找到拥有匹配技能的人员：

session.query(Person)./
join(Skill, Person.skills).filter(Skill.id.in_(list_of_skill_ids))

但是，使用in_条件会导致找到约翰，因为他有一个所需的技能。但搜索结果应该只显示拥有所有请求技能的人。简单来说，我想要的是类似in_()的功能，但要用和而不是或来匹配ID列表。我可能应该用这个来替换in_()部分，这样就不会直接使用ID列表：

filter(Person.id == 1, Person.id == 2)

那么，使用我从表单接收到的技能ID列表，最简单的构建查询的方法是什么呢？ID的数量可以是1到n。或者，这样查询一个父级是否正确？

Answer 1

一种方法是找出满足每个技能组合的人员列表，然后将所有列表进行交集。但这样做的缺点是，如果你要搜索10个技能组合，就需要发出10个查询。

更好的方法是根据人员ID对原始查询进行分组，然后计算每个人的技能数量。只保留那些技能数量与技能组合数量相匹配的人。也许可以这样做？

skill_count = session.query(Person.id.label('id'),func.count(Skill.id.distinct()).label('scount')).\
              select_from(Person).\
              join(Person.skills).\
              filter(Skill.id.in_(list_of_skill_ids)).\
              group_by(Person.id).all()
matching_pid_list = [x.id for x in skill_count if x.scount == len(list_of_skill_ids)]

Answer 2

最简单的解决方案是对每个技能使用 any。

list_of_skill_ids = [1, 2]
qry = session.query(Person)
for skill_id in list_of_skill_ids:
    qry = qry.filter(Person.skills.any(Skill.id == skill_id))

print('\n'.join("{}".format(_) for _ in qry.all()))

虽然对于大型数据集来说，这个查询可能不是性能最好的，但它的写法非常简洁。

另外，下面的写法也应该可以用。（和RedBaron的回答类似，但直接在查询中进行了检查）：

qry = (session.query(Person)
        .join(Skill, Person.skills)
        .filter(Skill.id.in_(list_of_skill_ids))
        .group_by(Person)
        .having(len(list_of_skill_ids) <= func.count(Skill.id))
    )

print('\n'.join("{}".format(_) for _ in qry.all()))