带额外连接条件的SQLAlchemy manytomy关系

Question

我在父母和孩子之间有一种多对多的关系。 在我的视图.py我知道：

parents = s.query(Parent)

在我的（Jinja2）模板中，我执行以下操作：

for parent in parents:
    print("parent {}".format(parent.name))
    if parent.children:
        for child in parent.children:
            print("- has child {}, age {}, {}" \
                .format(child.name, child.age, child.hair_color))

这显示了所有有或没有孩子的父母，工作正常。你知道吗

现在我想有相同的名单，所有父母（如上所述），但只有7岁以上的孩子和金发。会有带孩子和不带孩子的父母。 问题：如何在SQLAlchemy中而不是在普通SQL中做到这一点？你知道吗

我的型号：

# many-to-many link table: parent - child
parent_mtm_child_table = Table('parent_mtm_child', Base.metadata,
    Column('parent_id', Integer, ForeignKey('parent.id')),
    Column('child_id', Integer, ForeignKey('child.id'))
)

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    # many-to-many relationship with child
    children = relationship(
        'Child', 
        secondary=parent_mtm_child_table,
        back_populates='parents')

class Child(Base):
    __tablename__ = 'child'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    hair_color = Column(String)
    # many-to-many relationship with parent
    parents = relationship(
        'Parent', 
        secondary=parent_mtm_child_table,
        back_populates='children')

john = Parent(name='John')
mary = Parent(name='Mary')
gina = Parent(name='Gina')
liam = Child(name='Liam', age=6, hair_color='brown')
emma = Child(name='Emma', age=8, hair_color='blond')
lauren = Child(name='Lauren', age=9, hair_color='blond')
john.children.append(liam)
john.children.append(emma)
mary.children.append(liam)
gina.children.append(lauren)
s.add_all([john, mary, gina, liam, emma, lauren])
s.commit()

下一个查询错误，但给出了我可以检查的生成的sql：

r = s.query(Parent).outerjoin(Parent.children).filter(Child.age >= 7)

sql按预期在WHERE部分显示额外条件：

sql = 'SELECT parent.id AS parent_id, parent.name AS parent_name \
FROM parent LEFT OUTER JOIN (parent_mtm_child AS parent_mtm_child_1 
JOIN child ON child.id = parent_mtm_child_1.child_id) 
ON parent.id = parent_mtm_child_1.parent_id \
WHERE child.age >= 7'

将附加条件移到上一部分后，sql将变为：

sql = 'SELECT parent.id AS parent_id, parent.name AS parent_name, child.id AS child_id, child.name AS child_name, child.age AS child_age \
FROM parent \
LEFT OUTER JOIN (parent_mtm_child AS parent_mtm_child_1 \
JOIN child ON ((child.id = parent_mtm_child_1.child_id) AND (child.age >= 7)) ) \
ON parent.id = parent_mtm_child_1.parent_id'

现在执行原始sql：

with engine.connect() as con:
    r = con.execute(sql)
    for row in r:
        print("row = {}".format(row))

结果正确：

row = (1, 'John', 2, 'Emma', 8)
row = (2, 'Mary', None, None, None)
row = (3, 'Gina', 3, 'Lauren', 9)

所以这可能是有可能的。你知道吗

我如何在SQLAlchemy中使用额外的条件来实现这一点？你知道吗

我也尝试过这个方法：

r = s.query(Parent).join(Parent.children).\
            filter(Child.age >= 7).\
            options(contains_eager(Parent.children)).all()

但它给出了错误的结果：

parent John
- has child Emma, age 8, blond
- has child Liam, age 6, brown
parent Mary
- has child Liam, age 6, brown
parent Gina
- has child Lauren, age 9, blond

谢谢你的帮助。你知道吗