Python – How to return the count of related entities in sqlalchethe query


I'm new to sqlalchemy, and while the documentation seems fairly thorough, I couldn't find a way to do quite what I want.

Say I have two tables: forum and post. Each forum has a parent forum, and any number of posts. What I want is:

  • A list of top-level forums
  • Eagerly loaded child forums accessible through the top-level forums
  • A count of posts for each child forum

So I started with:


Which gives me all the top level forums. Of course accessing the child forums yields n select queries.


This solves the n select problem.

Now my best guess goes something like this:

 query(Forum, func.count(Forum.children.posts)).options(eagerload('children')).filter(Forum.parent==None).group_by(

But all I get is:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'posts'

I've tried a few variations, but haven't got any further. Just for clarity I'm looking for the equivalent of this SQL:

select Forum.*, Child.*, count(
from Forum
left join Forum Child on Child.parent =
left join Message on =
where Forum.parent is null
group by

Best Solution

Because you want the post count to be accessible on the child Forum objects you'll need to declare it as a column property when setting up the mappers. The column property declaration should look something like this (assuming you use declarative):

Forum.post_count = column_property(select([func.count()], ==

Then you can phrase your query like this:


Another option would be to select the children and counts separately. In this case you'll need to do the result grouping yourself:

ChildForum = aliased(Forum)
q = (query(Forum, ChildForum, func.count(
        .filter(Forum.parent == None)
        .outerjoin((ChildForum, Forum.children))
        .group_by(Forum, ChildForum)

from itertools import groupby
from operator import attrgetter

for forum, childforums in groupby(q, key=attrgetter('Node')):
    for _, child, post_count in childforums:
        if child is None:
            # No children
        # do something with child