SQLAlchemy query, join on relationship and order by count
flask, flask-sqlalchemy, python, sqlalchemy
Solution
The translation from raw query to Flask-SQLAlchemy is pretty much mechanical:
db.session.query(Post, db.func.count(Like.id)).outerjoin(Like).group_by(Post.id)
Problem
I have two SQLAlchemy models set up as follows: ``` ############## # Post Model # ############## class Post(db.Model): id = db.Column(db.Integer, primary_key = True) title = db.Column(db.String(250)) content = db.Column(db.String(5000)) timestamp = db.Column(db.Integer) author_id = db.Column(db.Integer, db.ForeignKey('user.id')) likes = db.relationship('Like', backref = 'post', lazy = 'dynamic') ############### # Likes Model # ############### class Like(db.Model): id = db.Column(db.Integer, primary_key = True) voter_id = db.Column(db.Integer, db.ForeignKey('user.id')) post_id = db.Column(db.Integer, db.ForeignKey('post.id')) ``` As you can see, there's a model for posts and a model for user likes on those posts. I'd like to create a query that selects all posts, ordered by the number of likes that post has. In the shell, I can run: ``` SELECT post.*, count(like.id) AS num_likes FROM post LEFT JOIN like ON post.id = like.post_id GROUP BY post.id; ``` What's the equivalent SQLAlchemy command? Thanks!