sqlalchemy объединяется с суммой и количеством сгруппированных строк

Привет, я работаю над небольшой игрой с предсказаниями в flask с flask-sqlalchemy. У меня есть модель пользователя:

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    nick = db.Column(db.String(255), unique=True)
    bets = relationship('Bet', backref=backref("user"))

и моя модель Бет

class Bet(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    uid = db.Column(db.Integer, db.ForeignKey('user.id'))
    matchid = db.Column(db.Integer, db.ForeignKey('match.id'))
    points = db.Column(db.Integer)

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

toplist = db.session.query(User.nick, func.sum(Bet.points)).\
    join(User.bets).group_by(Bet.uid).order_by(func.sum(Bet.points).desc()).all()

Это работает довольно хорошо, теперь может случиться так, что у двух игроков будет одинаковая сумма очков. В этом случае количество правильных прогнозов (вознаграждаемых 3 баллами) будет определять победителя. Я могу получить этот список по

tophits = db.session.query(User.nick, func.count(Bet.points)).\
            join(User.bets).filter_by(points=3).all()

Они оба работают хорошо, но я думаю, что должен быть способ собрать оба запроса вместе и получить таблицу с именем пользователя, очками и «количеством обращений». Я делал это раньше в SQL, но я не так хорошо знаком с SQLAlchemy, и у меня в голове путаются мысли. Как я могу получить оба запроса в одном?


sql sqlalchemy flask-sqlalchemy
person bknux    schedule 21.01.2014    source источник

Ответы (1)


arrow_upward
11
arrow_downward

В запросе для tophits просто замените конструкцию COUNT/filter_by эквивалентной SUM(CASE(..)) без filter, чтобы предложение WHERE для обоих было одинаковым. Код ниже должен сделать это:

total_points = func.sum(Bet.points).label("total_points")
total_hits = func.sum(case(value=Bet.points, whens={3: 1}, else_=0)).label("total_hits")
q = (session.query(
        User.nick,
        total_points,
        total_hits,
        )
    .join(User.bets)
    .group_by(User.nick)
    .order_by(total_points.desc())
    .order_by(total_hits.desc())
    )

Обратите внимание, что я изменил предложение group_by, чтобы использовать столбец, который находится в SELECT, поскольку в противном случае некоторые механизмы баз данных могут жаловаться. Но вам не нужно этого делать.

person van    schedule 22.01.2014
comment
это довольно близко, но он суммирует все очки для каждого пользователя, если у пользователя1 есть две ставки по 3 очка, а у пользователя2 есть две ставки одна 2 очка одна 1 очко, вывод будет [(u'user1',9,2),( u'пользователь2',9,2)] - person bknux; 22.01.2014
comment
Итак, это работает: ¸toplist = db.session.query(User.nick, func.sum(Bet.points), func.sum(case(value=Bet.points, whens={3: 1}, else_=0 ))).\ join(User.bets).group_by(Bet.uid).order_by(func.sum(Bet.points).desc()).all()¸ - person bknux; 22.01.2014
comment
Вы правы: пропустил join(User.bets) (добавлено в ответ). - person van; 22.01.2014
comment
... не забудьте добавить второй order_by - person van; 22.01.2014