筛选SQLAlchemy查询结果对象的一对多属性 [英] Filter SQLAlchemy query result object's one-to-many attribute

问题描述

说我有几个对象，具有一对多关系，例如

Say I have a couple objects, having a one-to-many relationship, something like

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

现在，我想查询父对象，但按child_type过滤其子对象，即类似

Now, I want to query Parent objects, but have their children filtered by child_type, ie something like

session.query(Parent).join(Parent.children).filter(Child.child_type == "a")

这只会返回带有所有子项的父项，基本上不考虑过滤器.这是完全有可能的结果，还是我还必须查询Child?

This just returns the Parent with all the children, basically disregarding the filter. Is this result at all possible or do I have to also query Child?

推荐答案

实际上，您的查询添加了一个联接和一个过滤器，但仅返回了Parent个实例.实际上，仅具有至少一个Child类型为a的Parent实例.
然后，当您在每个父级上访问.children时，将发出新的SQL语句，并加载该父级的所有子级.您可以再次在内存中应用过滤器，或创建自己的查询，而不必依赖关系导航(注释掉)，如下所示:

Indeed, your query adds a join and a filter, but returns only Parent instances. In fact, only those Parent instances which have at least one Child of type a.
When you then access .children on each of those parents, a new SQL statement will be issued and all children of that parent will be loaded. You can apply the filter again in memory, or create your own query and not rely on the relationship navigation (commented out) as below:

# 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 个孩子.您可以在执行查询然后丢弃/回收会话的情况下使用此方法:

A way to do it in one query is shown below, but be careful as you are effectively telling sqlalchemy that you loaded all children for parents. You can use this approach for scenarios where you perform your query and then discard/recycle the session:

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

这篇关于筛选SQLAlchemy查询结果对象的一对多属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！