在SQLAlchemy中按子查询排序

本文介绍了在SQLAlchemy中按子查询排序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我试图选择按最新答复时间降序排列的最新线程(Thread)(答复是帖子"模型，这是标准的论坛查询).在SQL中，我会这样写:

I'm trying to select the newest threads (Thread) ordered descending by the time of the most recent reply to them (the reply is a Post model, that's a standard forum query). In SQL I'd write it like this:

SELECT * FROM thread AS t ORDER BY (SELECT MAX(posted_at) FROM post WHERE thread_id = t.id) DESC

我如何在SQLAlchemy中进行此类操作?我尝试过这样的事情:

How do I do such thing in SQLAlchemy? I tried something like this:

scalar = db.select[func.max(Post.posted_at)].where(Post.thread_id == Thread.id).as_scalar() threads = Thread.query.order_by(scalar.desc()).all()

但是似乎我不理解标量是如何工作的.第5次阅读文档无济于事.有人可以帮我在SQLAlchemy中编写这样的查询吗?我为此应用程序使用flask-sqlalchemy和MySQL.

But it seems that I don't understand how scalars work. Reading docs for the 5th time won't help. Could someone help me write such query in SQLAlchemy? I use flask-sqlalchemy and MySQL for this app.

推荐答案

在我看来很好，这是一个测试:

looks fine to me, here's a test:

from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class Thread(Base): __tablename__ = 'thread' id = Column(Integer, primary_key=True) class Post(Base): __tablename__ = 'post' id = Column(Integer, primary_key=True) thread_id = Column(Integer, ForeignKey('thread.id')) posted_at = Column(String) s = Session() scalar = select([func.max(Post.posted_at)]).where(Post.thread_id == Thread.id).as_scalar() q = s.query(Thread).order_by(scalar.desc()) print q

输出(注意，我们只是在这里打印SQL):

output (note we're just printing the SQL here):

SELECT thread.id AS thread_id FROM thread ORDER BY (SELECT max(post.posted_at) AS max_1 FROM post WHERE post.thread_id = thread.id) DESC

看起来很像您的查询