How to return the count of related entities in sqlalchemy query

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()],        Message.__table__.c.forum == Forum.__table__.c.id    ).correlate(Forum.__table__).as_scalar().label('post_count'),    deferred=True)

Then you can phrase your query like this:

query(Forum).filter_by(parent=None).options(    eagerload('children'),    undefer('children.post_count'))

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(Message.id))        .filter(Forum.parent == None)        .outerjoin((ChildForum, Forum.children))        .outerjoin(ChildForum.posts)        .group_by(Forum, ChildForum)    )from itertools import groupbyfrom operator import attrgetterfor forum, childforums in groupby(q, key=attrgetter('Node')):    for _, child, post_count in childforums:        if child is None:            # No children            break        # do something with child