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
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()
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
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
looks pretty much like your query