Sqlalchemy: Get child count without reading all the children

user_2166337 0
Viewed 154

Here are my Parent and Child classes:

class Parent(Base):
    id = Column(...)
    ...

    children = relationship("Child", backref="parent", lazy="select")

class Child(Base):
    id = Column(...)
    parent_id = Column(...)
    active = Column(Boolean(), ...)

The reason behind the loading technique of children of Parent being lazy is that there can be a very large number of children associated with a parent.

Now, I would like to get the number of active children of a parent as a hybrid-property. Here is how I tried to do it:

class Parent(Base):
    ...

    @hybrid_property
    def active_count(self):
        return len([child for child in self.children if child.active])

    @active_count.expression
    def active_count(cls):
        return (
            select(func.count(Child.id))
            .where(Child.parent_id == cls.id)
            .where(Child.active == True)
        )

But the problem with this method is that when I call parent.active_count, it fires a query to get all of the children.

How can I get only the count (of active children) without reading whole the children?

sqlalchemy python
1 Answers

I think you unnecessary iterate over children within active_count hybrid_property definition. This should works for you:

class Parent(Base):
    ...
    children = relationship("Child", backref="parent", lazy="dynamic")

    @hybrid_property
    def active_count(self):
        return self.children.filter_by(active=True).with_entities(func.count('*')).scalar()
        # or 
        # return self.children.filter_by(active=True).count()
        # but it would have worse performance
user_79384350
Related