python过滤sql_python – 过滤SQLAlchemy查询结果对象的一对多属性

实际上,您的查询会添加连接和过滤器,但只返回父实例.实际上,只有那些至少有一个类型为a的子类的父实例.

然后,当您访问每个父节点上的.children时,将发出一个新的SQL语句,并且将加载该父节点的所有子节点.您可以在内存中再次应用过滤器,或创建自己的查询,而不是依赖关系导航(注释掉),如下所示：

# 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您为父母加载了所有子项.您可以将此方法用于执行查询然后丢弃/回收会话的方案：

# 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)