我有两个如下所示的SQLAlchemy模型:
##############
# 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'))
你可以看到,有一个帖子模型和一个用户对这些帖子点赞的模型。我想创建一个查询来选择所有帖子,并按该帖子具有的点赞数量排序。在 shell 中,我可以运行:
SELECT post.*,
count(like.id) AS num_likes
FROM post
LEFT JOIN like
ON post.id = like.post_id
GROUP BY post.id;
SQLAlchemy 命令的等价语句是什么?
谢谢!
group_by()
方法。在使用SQLAlchemy创建此查询时,您是否遇到了任何特定的问题? - Mark Hildreth