SQLAlchemy filter query by related object

Question

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  

My current query is:

 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.

EDIT: 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

Answer 1

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

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)

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

Answer 2

You will need to use a subquery in order to compute the aggregate score for each user. Subqueries are described here: http://www.sqlalchemy.org/docs/05/ormtutorial.html?highlight=subquery#using-subqueries