title: How to return the count of related entities in sqlalchemy query
body: <p>I'm new to sqlalchemy, and while the documentation seems fairly thorough, I couldn't find a way to do quite what I want.</p>
  
  <p>Say I have two tables: forum and post. Each forum has a parent forum, and any number of posts. What I want is:</p>
  
  <ul>
  <li>A list of top-level forums</li>
  <li>Eagerly loaded child forums accessible through the top-level forums</li>
  <li>A count of posts for each <em>child</em> forum</li>
  </ul>
  
  <p>So I started with:</p>
  
  <pre><code> query(Forum).filter(Forum.parent==None).all()
  </code></pre>
  
  <p>Which gives me all the top level forums. Of course accessing the child forums yields n select queries.</p>
  
  <pre><code> query(Forum).options(eagerload('children')).filter(Forum.parent==None).all()
  </code></pre>
  
  <p>This solves the n select problem.</p>
  
  <p>Now my best guess goes something like this:</p>
  
  <pre><code> query(Forum, func.count(Forum.children.posts)).options(eagerload('children')).filter(Forum.parent==None).group_by(Forum.children.id).all()
  </code></pre>
  
  <p>But all I get is:</p>
  
  <pre><code>AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'posts'
  </code></pre>
  
  <p>I've tried a few variations, but haven't got any further. Just for clarity I'm looking for the equivalent of this SQL:</p>
  
  <pre><code>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
  </code></pre>