SQLAlchemy查询,加入关系和数量的顺序 [英] SQLAlchemy query, join on relationship and order by count
本文介绍了SQLAlchemy查询,加入关系和数量的顺序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个SQLAlchemy模型设置如下:
############# #
#发布模型#
############## $ b $ 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#
######### ###### $ b $ 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中,我可以运行:
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
FROM post
LEFT JOIN like
on post.id = like.post_id
GROUP BY post.id;
等价的SQLAlchemy命令是什么?
解决方案
原始查询翻译成Flask-SQLAlchemy非常机械:
db.session.query(Post,db.func.count(Like.id))。outerjoin(Like).group_by(Post.id)
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!
解决方案
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)
这篇关于SQLAlchemy查询,加入关系和数量的顺序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文