How to write a hybrid property that depends on a column in relation to children?

Question

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 request a property for whether the parent has a child who is a boy? Hopefully this column is used in pandas, but not sure how to query it efficiently. My intuition is to create a hybrid SQLALchemy has_a_boy_child property, but I'm not sure how to define a hybrid property or an appropriate expression. Thanks!

Answer 1

Correlated Subquery Relationship Hybrid, , count :

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

:

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

. bool count ( None na pandas), , :

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