SQLAlchemy query for relationship counted object

Question

Suppose I have an SQL Alchemy ORM class:

class Session(db.Model): id = db.Column(db.Integer, primary_key=True) user_agent = db.Column(db.Text, nullable=False) class Run(db.Model): id = db.Column(db.Integer, primary_key=True) session_id = db.Column(db.Integer, db.ForeignKey('session.id')) session = db.relationship('Session', backref=db.backref('runs', lazy='dynamic')) 

And I want to essentially request the following:

 ((session.id, session.user_agent, session.runs.count()) for session in Session.query.order_by(Session.id.desc())) 

However, this is clearly 1 + n requests, which is terrible. What is the correct way to do this with 1 query? In regular SQL, I would do this with something like:

 SELECT session.id, session.user_agent, COUNT(row.id) FROM session LEFT JOIN rows on session.id = rows.session_id GROUP BY session.id ORDER BY session.id DESC 

Answer 1

Build a subquery that groups and counts session identifiers from runs and joins this in your final query.

 sq = session.query(Run.session_id, func.count(Run.session_id).label('count')).group_by(Run.session_id).subquery() result = session.query(Session, sq.c.count).join(sq, sq.c.session_id == Session.id).all()