How to return the count of related entities in sqlalchemy 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 ha开发者_如何学Gos 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:
query(Forum).filter(Forum.parent==None).all()
Which gives me all the top level forums. Of course accessing the child forums yields n select queries.
query(Forum).options(eagerload('children')).filter(Forum.parent==None).all()
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(Forum.children.id).all()
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(Post.id)
from Forum
left join Forum Child on Child.parent = Forum.id
left join Message on Message.forum = Child.id
where Forum.parent is null
group by Child.id
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 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
break
# do something with child
精彩评论