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()
Я знаю, что это ясно не корректно, это - просто мое лучшее предположение. Однако после рассмотрения документации и поиска с помощью Google я не могу найти ответ.
Править: Возможно, помогло бы, делал ли я набросок того, на что был бы похож запрос 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
3 ответа
] Однослойный путь, с добавлением []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))
]Вышеуказанные два варианта неэффективны в том, что вы группируете по всем столбцам "user" (или вы используете MySQL's "группировать только по нескольким столбцам", который является только MySQL). Чтобы минимизировать это, в учебнике ORM используется подзапросный подход:[
] [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 at: []http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#selecting-entities-from-subqueries[][
]Вам нужно будет использовать подзапрос, чтобы вычислить суммарную оценку для каждого пользователя. Подзапросы описаны здесь: http://www.sqlalchemy.org/docs/05/ormtutorial.html? highlight=subquery#using-subqueries
Я предполагаю, что столбец (не связь), который вы используете для соединения, называется 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())
Надежда, что это поможет.