SQLAlchemy - WHERE 子句中的子查询 [英] SQLAlchemy - subquery in a WHERE clause
问题描述
我最近才开始使用 SQLAlchemy,但仍然无法理解一些概念.
归结为基本元素,我有两个这样的表(这是通过 Flask-SQLAlchemy):
class User(db.Model):__表名__ = '用户'user_id = db.Column(db.Integer, primary_key=True)类帖子(db.Model):__tablename__ = '帖子'post_id = db.Column(db.Integer, primary_key=True)user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))post_time = db.Column(db.DateTime)user = db.relationship('用户', backref='posts')
我将如何查询用户列表及其最新帖子(不包括没有帖子的用户).如果我使用 SQL,我会这样做:
SELECT [随便]FROM 帖子 AS p左加入用户作为 u ON u.user_id = p.user_idWHERE p.post_time = (SELECT MAX(post_time) FROM posts WHERE user_id = u.user_id)
所以我确切地知道想要的"SQL 以获得我想要的效果,但不知道如何在 SQLAlchemy 中正确地"表达它.
以防万一,我使用的是 SQLAlchemy 0.6.6.
这应该可以工作(不同的 SQL,相同的结果):
t = Session.query(Posts.user_id,func.max(Posts.post_time).label('max_post_time'),).group_by(Posts.user_id).subquery('t')查询 = Session.query(User, Posts).filter(and_(User.user_id == Posts.user_id,User.user_id == t.c.user_id,Posts.post_time == t.c.max_post_time,))对于用户,在查询中发布:打印 user.user_id, post.post_id
<块引用>
其中 c 代表列"
I've just recently started using SQLAlchemy and am still having trouble wrapping my head around some of the concepts.
Boiled down to the essential elements, I have two tables like this (this is through Flask-SQLAlchemy):
class User(db.Model):
__tablename__ = 'users'
user_id = db.Column(db.Integer, primary_key=True)
class Posts(db.Model):
__tablename__ = 'posts'
post_id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))
post_time = db.Column(db.DateTime)
user = db.relationship('User', backref='posts')
How would I go about querying for a list of users and their newest post (excluding users with no posts). If I was using SQL, I would do:
SELECT [whatever]
FROM posts AS p
LEFT JOIN users AS u ON u.user_id = p.user_id
WHERE p.post_time = (SELECT MAX(post_time) FROM posts WHERE user_id = u.user_id)
So I know exactly the "desired" SQL to get the effect I want, but no idea how to express it "properly" in SQLAlchemy.
Edit: in case it's important, I'm on SQLAlchemy 0.6.6.
This should work (different SQL, same result):
t = Session.query(
Posts.user_id,
func.max(Posts.post_time).label('max_post_time'),
).group_by(Posts.user_id).subquery('t')
query = Session.query(User, Posts).filter(and_(
User.user_id == Posts.user_id,
User.user_id == t.c.user_id,
Posts.post_time == t.c.max_post_time,
))
for user, post in query:
print user.user_id, post.post_id
Where c stands for 'columns'
这篇关于SQLAlchemy - WHERE 子句中的子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!