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

在搜索页面上，用户选择一个或多个技能，并基于此创建person的搜索结果。假设一个用户想要找到一个既懂Python又懂SQLAlchemy的人。应用程序从表单submit中获得一个包含两个id的列表[1,2]。我得到了以下查询的工作种类，寻找具有匹配技能的人:

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

然而，使用in_子句导致John person被发现，因为他拥有所需的技能之一。但是搜索结果应该只显示具备所有要求技能的人。基本上我想要的是类似in_()的东西，但哪个用户和而不是或来匹配id列表。我可能应该将in_()部分替换为这个，它不直接接受id列表:

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

但是，使用我从表单收到的技能id列表以编程方式构建查询的最简单方法是什么?id个数为1 ~ n。或者这是通过父节点的子节点查询父节点的正确方式吗?

Answer 1

最直接的SA解决方案是对每个技能使用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()))

Answer 2

一种方法可以是找到满足每个技能集的人员列表，然后取所有列表的交集。但这种情况的缺点是，如果您必须搜索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)]