Let's say I have two tables (using SQLAlchemy) for parents and children:

class Child(Base):__tablename__ = 'Child'id = Column(Integer, primary_key=True) is_boy = Column(Boolean, default=False)parent_id = Column(Integer, ForeignKey('Parent.id'))class Parent(Base):__tablename__ = 'Parent'id = Column(Integer, primary_key=True) children = relationship("Child", backref="parent")

How can I query a property for whether the parent has any child that is a boy? Hoping to use this column in pandas but not sure how to effectively query it. My intuition is to create a SQLALchemy hybrid property has_a_boy_child, but I am not sure how to define the hybrid property or the matching expression. Thanks!

Following Correlated Subquery Relationship Hybrid example, I would build a property which returns count of boy children:

@hybrid_property
def has_a_boy_child(self):return any(child.is_boy for child in self.children)@has_a_boy_child.expression
def has_a_boy_child(cls):return (select([func.count(Child.id)]).where(Child.parent_id == cls.id).where(Child.is_boy == True).label("number_of_boy_children"))

And you can use it like:

q_has_boys = session.query(Parent).filter(Parent.has_a_boy_child).all()
q_no_boys = session.query(Parent).filter(~Parent.has_a_boy_child).all()
q_attr = session.query(Parent, Parent.has_a_boy_child).all()

Update: If you really would like a bool instead of count (where None would be na in pandas), you can do it as shown below:

@has_a_boy_child.expression
def has_a_boy_child(cls):return (select([case([(exists().where(and_(Child.parent_id == cls.id,Child.is_boy == True,)).correlate(cls), True)],else_=False,).label("has_boys")]).label("number_of_boy_children"))