title: Counting relationships in SQLAlchemy
body: <p>My SQLAlchemy structure looks like this</p>
  
  <pre><code>papers2authors_table = Table('papers2authors', Base.metadata,
      Column('paper_id', Integer, ForeignKey('papers.id')),
      Column('author_id', Integer, ForeignKey('authors.id'))
  )
  
  class Paper(Base):
      __tablename__ = "papers"
  
      id = Column(Integer, primary_key=True)
      title = Column(String)
      handle = Column(String)
  
      authors = relationship("Author",
                      secondary="papers2authors",
                      backref="papers")
  
  class Author(Base):
      __tablename__ = "authors"
  
      id = Column(Integer, primary_key=True)
      name = Column(String, unique=True)
      code = Column(String, unique=True)
  </code></pre>
  
  <p>I would like to query two things:</p>
  
  <ol>
  <li>The numbers of authors in each paper</li>
  <li>The numbers of papers each author has (partly answered <a href="https://stackoverflow.com/questions/11567666/count-number-of-rows-in-a-many-to-many-relationship-sqlalchemy">here</a>)</li>
  </ol>
  
  <p>I tried many options with <code>func.count()</code> and <code>count()</code>, but they return nonsensical results. How to do these two things in an SQLAlchemy way?</p>
  
  <p><strong>What I tried</strong></p>
  
  <ul>
  <li><code>db.s.query(func.count(core.Paper.id)).group_by(core.Author.id).first()</code> = <code>sqlalchemy.exc.OperationalError: (OperationalError) no such column: authors.id</code></li>
  <li><code>db.s.query(func.count(core.Author.papers)).group_by(core.Author.id).first()</code> = <code>(128100)</code>, which doesn't what I expected</li>
  <li><code>db.s.query(core.Author.papers).group_by(core.Author.id).count().first()</code> = <code>AttributeError: 'int' object has no attribute 'first'</code></li>
  <li>...</li>
  </ul>