假设我有几个对象,具有一对多的关系,例如
class Parent():
//id, other cols, etc
children = relationship("Child", backref="parent")
class Child():
parent_id = Column(Integer, ForeignKey("parent.id")
child_type = Column(Enum("a","b"))
现在,我想查询 Parent 对象,但让他们的孩子按 child_type 过滤,即类似
session.query(Parent).join(Parent.children).filter(Child.child_type == "a")
这只是返回父级和所有子级,基本上忽略了过滤器。这个结果是否可能,或者我是否还必须查询 Child?
事实上,您的查询添加了联接和过滤器,但仅返回Parent
实例。实际上,only those Parent
至少有一个实例Child
类型的a
.
当您随后访问.children
在每个父级上,将发出一个新的 SQL 语句,并且all该父级的子级将被加载。您可以在内存中再次应用过滤器,或者创建自己的查询而不依赖于关系导航(已注释掉),如下所示:
# select *only* those parents who have at least one child of type "a"
parents = session.query(Parent).join(Parent.children).filter(Child.child_type == "a")
for p in parents:
# 1. in-memory filter: now select only type "a" children for each parent
children_a = [c for c in p.children if c.child_type == 'a']
# 2. custom query: now select only type "a" children for each parent
# children_a = session.query(Child).with_parent(p).filter(Child.child_type == "a")
print("AAA", p)
for c in children_a:
print("AAA ..", c)
下面显示了在一个查询中执行此操作的一种方法,但要小心,因为您实际上是在告诉 sqlalchemy 您已加载all孩子为父母。您可以在执行查询然后丢弃/回收会话的情况下使用此方法:
# select all parents, and eager-load children of type "a"
parents = (session.query(Parent)
.join(Parent.children).filter(Child.child_type == "a")
# make SA think we loaded all *parent.children* collection
.options(contains_eager('children'))
)
for p in parents:
children_a = p.children # now *children* are *incorrectly* filtered
print("BBB", p)
for c in children_a:
print("BBB ..", c)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)