sqlalchemy 连接分组行的总和和计数

Question

嗨，我正在使用flask-sqlalchemy 在flask 中开发一个小预测游戏我有一个用户模型:

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

和我的 Bet 模型

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)

两者都不是完整的类(class)，但它应该为这个问题做。用户可以收集积分来预测比赛结果，并获得不同数量的积分来预测确切的结果、获胜者或差异。
我现在想要一个顶级用户的列表，我必须在其中总结我正在通过的要点

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 不太熟悉，并且在我的大脑中思考了结。如何将两个查询合二为一？

Answer 1

在查询 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 中的列，因为某些数据库引擎可能会提示。但你不需要这样做。