CODEWITHSUNDEEP
pythonsqlalchemyflaskflask-sqlalchemy
sundance
sundance

Reputation: 2945

SQLAlchemy query, join on relationship and order by count

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!

Upvotes: 12

Views: 11446

Answers (1)

Miguel Grinberg
Miguel Grinberg

Reputation: 67479

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)

Upvotes: 12

Related Questions