概述
users: id user_name score scores: user score_amount created
我当前的查询是:
top_users = DBSession.query(User).options(eagerload('scores')).filter_by(User.scores.created > somedate).order_by(func.sum(User.scores).desc()).all()
我知道这显然不正确,这只是我最好的猜测.然而,在查看文档和谷歌搜索后,我找不到答案.
SELECT user.*,SUM(scores.amount) as score_increase FROM user LEFT JOIN scores ON scores.user_id = user.user_id WITH scores.created_at > someday ORDER BY score_increase DESC
sess.query(User,func.sum(score.amount).label('score_increase')).\ join(User.scores).\ filter(score.created_at > someday).\ group_by(User).\ order_by("score increase desc")
或者如果您只想要结果中的用户:
sess.query(User).\ join(User.scores).\ filter(score.created_at > someday).\ group_by(User).\ order_by(func.sum(score.amount))
上述两个都没有效率,因为您将所有“用户”列分组(或者您只使用MysqL的“只有几列”组,这仅是MysqL).为了最小化,子查询方法:
subq = sess.query(score.user_id,func.sum(score.amount).label('score_increase')).\ filter(score.created_at > someday).\ group_by(score.user_id).subquery() sess.query(User).join((subq,subq.c.user_id==User.user_id)).order_by(subq.c.score_increase)
相同场景的示例在ORM教程中:http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#selecting-entities-from-subqueries
总结
以上是编程之家为你收集整理的python – SQLAlchemy过滤器查询由相关对象全部内容,希望文章能够帮你解决python – SQLAlchemy过滤器查询由相关对象所遇到的程序开发问题。
如果您也喜欢它,动动您的小指点个赞吧