我试图选择按最新答复时间降序排列的最新线程(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看起来很像您的查询
更多推荐
在SQLAlchemy中按子查询排序
发布评论