Counting relationships in SQLAlchemy

Question

My SQLAlchemy structure looks like

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) 

I would like to request two things:

• The number of authors in each article
• The number of articles that each author has (partially answered here )

I tried many options with func.count() and count() , but they return meaningless results. How to do these two things in SQLAlchemy way?

What i tried

• db.s.query(func.count(core.Paper.id)).group_by(core.Author.id).first() = sqlalchemy.exc.OperationalError: (OperationalError) no such column: authors.id
• db.s.query(func.count(core.Author.papers)).group_by(core.Author.id).first() = (128100) , which does not match the expected
• db.s.query(core.Author.papers).group_by(core.Author.id).count().first() = AttributeError: 'int' object has no attribute 'first'
• ...

Answer 1

Found solutions:

• The number of authors in each article: db.s.query(core.Paper.title, func.count(core.Author.id)).join(core.Paper.authors).group_by(core.Paper.id).all()
• The number of documents each author has: db.s.query(core.Author.name, func.count(core.Author.id)).join(core.Author.papers).group_by(core.Author.id).all()

Relevant: http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.having