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

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

Answer 2

I am assuming the column (not the relation) you're using for the join is called Score.user_id, so change it if this is not the case.

You will need to do something like this:

DBSession.query(Score.user_id, func.sum(Score.score_amount).label('total_score')).group_by(Score.user_id).filter(Score.created > somedate).order_by('total_score DESC')[:10]

However this will result in tuples of (user_id, total_score). I'm not sure if the computed score is actually important to you, but if it is, you will probably want to do something like this:

users_scores = []
q = DBSession.query(Score.user_id, func.sum(Score.score_amount).label('total_score')).group_by(Score.user_id).filter(Score.created > somedate).order_by('total_score DESC')[:10]
for user_id, total_score in q:
    user = DBSession.query(User)
    users_scores.append((user, total_score))

This will result in 11 queries being executed, however. It is possible to do it all in a single query, but due to various limitations in SQLAlchemy, it will likely create a very ugly multi-join query or subquery (dependent on engine) and it won't be very performant.

If you plan on doing something like this often and you have a large amount of scores, consider denormalizing the current score onto the user table. It's more work to upkeep, but will result in a single non-join query like:

DBSession.query(User).order_by(User.computed_score.desc())

Hope that helps.

Answer 3

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://www.sqlalchemy.org/docs/05/ormtutorial.html#using-subqueries.