Markus Meskanen Markus Meskanen - 23 days ago 8
Python Question

Children count not fetched although query fetches everything else

I have a function like so:

@app.route('/search/')
def search():
# Handle search conditions
query = db.session.query(
Parent.id,
Parent.name,
func.count(Children.id),
)
query = query.filter(and_(*conditions)) # This comes from "Handle search conditions"
query = query.outerjoin((Children, Parent.children))
return jsonify([row._asdict() for row in query.all()])


And when called, it properly returns the parents' data:

[
{
'id': 1,
'name': 'Example Parent',
},
{
'id': 2,
'name': 'Another Parent',
}
]


But it doesn't return the count of its children.

What did I do wrong, how do I make it also fetch the child count?

Answer

You need to set the label for the count column

query = db.session.query(
    Parent.id,
    Parent.name,
    func.count(Children.id).label('count'),
)