Запрос фильтра SQLAlchemy связанным объектом

Используя SQLAlchemy, у меня есть отношение "один к большому" с двумя таблицами - пользователями и оценками. Я пытаюсь запросить 10 лучших пользователей, отсортированных по их суммарному баллу за прошлые X дней.

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

Я знаю, что это явно не правильно, это просто мое лучшее предположение. Однако, посмотрев документацию и поисковый запрос, я не могу найти ответ.

EDIT: Возможно, это помогло бы, если бы я набросал, как выглядит запрос MySQL:

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

Ответ 1

Однострочный путь с добавлением group_by для всех пользовательских столбцов, хотя MySQL позволит вам группировать только столбец "id", если вы выберете:

    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

Ответ 2

Вам нужно будет использовать подзапрос, чтобы вычислить совокупную оценку для каждого пользователя. Подзапросы описаны здесь: http://www.sqlalchemy.org/docs/05/ormtutorial.html?highlight=subquery#using-subqueries

Ответ 3

Я предполагаю, что столбец (а не отношение), который вы используете для соединения, называется Score.user_id, поэтому измените его, если это не так.

Вам нужно будет сделать что-то вроде этого:

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]

Однако это приведет к кортежам (user_id, total_score). Я не уверен, действительно ли рассчитанный результат действительно важен для вас, но если это так, вы, вероятно, захотите сделать что-то вроде этого:

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

Это приведет к выполнению 11 запросов. Можно сделать все это в одном запросе, но из-за различных ограничений в SQLAlchemy он, скорее всего, создаст очень уродливый запрос или подзапрос нескольких подключений (в зависимости от движка), и он не будет очень результативным.

Если вы планируете часто делать что-то подобное, и у вас есть большое количество баллов, рассмотрите вопрос о денормализации текущего счета на таблицу пользователя. Это больше работает для поддержки, но приведет к одному запросу без объединения, например:

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

Надеюсь, что это поможет.