Join and count in sql-alchemy

Question

I have a model defined like so:

class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    authors = db.relationship('Author', secondary=articles_authors, backref=db.backref('articles', lazy='dynamic'))
    tags = db.relationship('Tag', secondary=articles_tags, backref=db.backref('articles', lazy='dynamic'))
    comments = db.relationship('Comment', backref=db.backref('article'), lazy='dynamic')
    creation_time = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    modification_time = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    title = db.Column(db.String(256), nullable=False)
    contents = db.Column(db.Text, nullable=False)

I'd like to create a query that would give me all articles joined with authors and tags and that would count comments related to the given article. That's what I figured out so far:

Article.query.options(db.joinedload(Article.authors), db.joinedload(Article.tags)).all()

What gives me trouble is the counting part - I couldn't find any examples on how to do it. So how do I do it?

EDIT:

Query that doesn't work, but feels like the right direction:

subquery = db.session.query(Comment.article_id, func.count(Comment.id).label('comments_count'))\
            .group_by(Comment.article_id).subquery()

return db.session.query(Article, subquery.c.comments_count)\
    .outerjoin(subquery, Article.id == subquery.c.article_id)\
    .join(Tag).all()

The counting part in this case works ok, but I'm not getting tags and authors using this query.

EDIT2:

If it's not obvious - relation between article and tag is many-to-many:

articles_tags = db.Table('articles_tags',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id')),
    db.Column('article_id', db.Integer, db.ForeignKey('article.id'))
)

The same goes for article and author.

EDIT:

The answer:

subquery = db.session.query(Comment.article_id, func.count(Comment.id).label('comments_count'))\
            .group_by(Comment.article_id).subquery()

return db.session.query(Article, subquery.c.comments_count)\
    .outerjoin(subquery, Article.id == subquery.c.article_id)\
    .options(db.joinedload(Article.authors), db.joinedload(Article.tags)).all()

Answer 1

I think what you are looking for might be something like this(?)

(
    session.query(Article, func.count(Comment.id))
    .select_from(Comment)
    .join(Comment.article)
    .group_by(Article)
)

To get the authors and tags you should be able to just use .authors or .tags on the Article objects. To load it eagerly, you could use joinedload or subqueryload.

EDIT:

(
    session.query(Article, func.count(Comment.id))
    .select_from(Comment)
    .join(Comment.article)
    .options(db.joinedload(Article.authors), db.joinedload(Article.tags))
    .group_by(Article)
)