mysql 按子查询排序,在SQLAlchemy中按子查询排序

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值