Filter SQLAlchemy query result object's one-to-many attribute

Question

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

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

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?

Answer 1

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)

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)