python - SQLAlchemy - list of parents with count of filtered children

Question

I'm using SQLAlchemy to try and retrieve a full list of parents, and a count of a filtered list of their children. I have a feeling it needs subqueries, but I'm not too hot on it.

For example, here are parents and children models:

class Parent(db.Model):
__tablename__ = "parent"

id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(32))



class Child(db.Model):
__tablename__ = "child"

id = db.Column(db.Integer, primary_key=True)
parent_id = db.Column(db.Integer, db.ForeignKey("parent.id")
child_type = db.Column(db.String(1))
name = db.Column(db.String(32))

parent = db.relationship("Parent", backref='children')

Let's say we have the following entries in here:

PARENT_1 - 3 children (type A, B, B)

PARENT_2 - 1 child (type A)

PARENT_3 - no children

I want a query that prints a list of ALL parents, and the numbers of children that are type B. So it would spit out:

PARENT_1, 2
PARENT_2, 0
PARENT_3, 0

This is what I was attempting so far, which works for when there are B type children, but won't include the parents without any.

counts = db.session.query(
   Parent.id,
   db.func.count(Child.id)
).join(
   Child, Child.parent_id==Parent.id
).filter(
   Child.child_type=='B'
).group_by(
   Parent.id
)

I tried making the filter or_(Child.child_type=='B', not_(Parent.children.any(Child.child_type=='s'))) but then it gives the total child count instead of the filtered one.

I hope this is clear. Where would I go from here?

Edit:

I think I've found a way to do it with subqueries, but if there are no children matching, it returns None instead of 0, so I presume there's still a better way... regardless:

subquery = db.session.query(
   Parent.id.label('parent_id'),
   db.func.count(Child.id).label('child_count')
).join(
   Child, Child.parent_id==Parent.id
).filter(
   Child.child_type=='B'
).group_by(
   Parent.id
).subquery()

counts = db.session.query(
   Parent.id,
   subquery.c.child_count
 ).outerjoin(
   subquery,
   Parent.id==subquery.c.parent_id
 )

question from:https://stackoverflow.com/questions/65925699/sqlalchemy-list-of-parents-with-count-of-filtered-children

Answer 1

Waitting for answers