SQLAlchemy 按相关对象过滤查询 [英] SQLAlchemy filter query by related object

问题描述

使用 SQLAlchemy，我与两个表(用户和分数)建立了一对多关系.我正在尝试查询按过去 X 天的总分排序的前 10 位用户.

Using SQLAlchemy, I have a one to many relation with two tables - users and scores. I am trying to query the top 10 users sorted by their aggregate score over the past X amount of days.

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()  

我知道这显然是不正确的，这只是我最好的猜测.但是，在查看文档和谷歌搜索后，我找不到答案.

I know this is clearly not correct, it's just my best guess. However, after looking at the documentation and googling I cannot find an answer.

如果我勾勒出 MySQL 查询的样子，也许会有所帮助:

Perhaps it would help if I sketched what the MySQL query would look like:

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

推荐答案

单连接行方式，为所有用户列添加了 group_by如果您选择id"列:

The single-joined-row way, with a group_by added in for all user columns although MySQL will let you group on just the "id" column if you choose:

    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")

或者，如果您只想要结果中的用户:

Or if you just want the users in the result:

sess.query(User).\
           join(User.scores).\
           filter(Score.created_at > someday).\
           group_by(User).\
           order_by(func.sum(Score.amount))

上述两个效率低下，因为您对用户"的所有列进行分组(或者您正在使用 MySQL 的仅对几列进行分组"，即仅限 MySQL).为了尽量减少这种情况，子查询方法:

The above two have an inefficiency in that you're grouping on all columns of "user" (or you're using MySQL's "group on only a few columns" thing, which is MySQL only). To minimize that, the subquery approach:

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

An example of the identical scenario is in the ORM tutorial at: http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#selecting-entities-from-subqueries

这篇关于SQLAlchemy 按相关对象过滤查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！