如何使用SQLAlchemy对* count *子查询求和? [英] How to sum *count* subqueries with SQLAlchemy?
问题描述
我的数据库中有以下模型(Flask-SQLALchemy,声明性方法,已简化):
I have the following models in my DB (Flask-SQLALchemy, declarative approach, simplified):
class Player(db.Model):
id = db.Column(db.Integer, primary_key = True)
...
class Game(db.Model):
id = db.Column(db.Integer, primary_key = True)
creator_id = db.Column(db.Integer, db.ForeignKey('player.id'))
creator = db.relationship(Player, foreign_keys='Game.creator_id')
opponent_id = db.Column(db.Integer, db.ForeignKey('player.id'))
opponent = db.relationship(Player, foreign_keys='Game.opponent_id')
winner = db.Column(db.Enum('creator', 'opponent'))
每局比赛都可能赢,输或平局.我需要让玩家按获胜率"对他们进行排序-即:
Each game may be either won, lost or ended in draw. I need to get players sorting them by "win rate" - i.e.:
- 如果玩家创建了游戏,而游戏的获胜者是
creator
,则视为获胜; - 如果玩家被邀请作为对手参加比赛,而游戏的获胜者是
对手
,则也视为获胜; - 该玩家参加的其他游戏被视为输掉游戏.
- if player created a game and that game's winner is
creator
, it is considered win; - if the player was invited to game as opponent and game's winner is
opponent
, it is also considered win; - other games where this player participated are considered lost games.
所以我的算法如下:
@hybrid_property
def winrate(self):
games = Game.query.filter(or_(
Game.creator_id == self.id,
Game.opponent_id == self.id,
))
count = 0
wins = 0
for game in games:
count += 1
if game.creator_id == self.id and game.winner == 'creator':
wins += 1
elif game.opponent_id == self.id and game.winner == 'opponent':
wins += 1
if count == 0:
return 0
return wins / count
当我要确定特定玩家的获胜率时,此方法有效.但是当我想按获胜率对玩家进行排序时,它失败了.我试图用SQL重写它,并得到了这样的内容:
This approach works when I want to determine win rate for particular player; but it fails when I want to sort players by win rate. I tried to rewrite it in SQL and got something like this:
SELECT * FROM player
ORDER BY ((SELECT count(g1.id) FROM game g1
WHERE g1.creator_id = player.id AND g1.winner = 'creator'
) + (SELECT count(g2.id) FROM game g2
WHERE g2.opponent_id = player.id AND g2.winner = 'opponent'
)) / (SELECT count(g3.id) FROM game g3
WHERE player.id IN (g3.creator_id, g3.opponent_id)
)
这不能处理没有游戏的玩家,但应该可以正常工作.没有游戏的玩家可能可以使用MySQL CASE
语句来处理.
This doesn't handle players without games but should work in general. Players without games can be probably handled with MySQL CASE
statement.
但是问题是我无法弄清楚如何使用SQLAlchemy对该SQL进行编码.这是我尝试使用的(简化)代码:
But the problem is that I cannot figure how do I encode this SQL using SQLAlchemy. Here is a (simplified) code I try to use:
@winrate.expression
def winrate(cls):
cnt = Game.query.filter(
cls.id.in_(Game.creator_id, Game.opponent_id)
).with_entities(func.count(Game.id))
won = Game.query.filter(
or_(
and_(
Game.creator_id == cls.id,
Game.winner == 'creator',
),
and_(
Game.opponent_id == cls.id,
Game.winner == 'opponent',
),
)
)
return case([
(count == 0, 0),
], else_ = (
won / count
))
当涉及到 won/count
行时,此代码失败,告诉我 Query
不能被 Query
除以.我尝试使用子查询,但没有成功.
This code fails when it comes to won / count
line telling me that Query
cannot be divided by Query
. I tried using subqueries but without any success.
我应该如何实施?还是我应该使用某种联接/无论使用什么?(无法更改数据库方案.)
How should I implement it? Or maybe I should use some kind of joins/whatever? (DB scheme cannot be changed.)
推荐答案
尝试使用核心表达式而不是orm查询:
Try working with core expressions instead of orm queries:
class Player(..):
# ...
@winrate.expression
def _winrate(cls):
cnt = (
select([db.func.count(Game.id)])
.where(
db.or_(
Game.creator_id == cls.id,
Game.opponent_id == cls.id,
))
.label("cnt")
)
won = (
select([db.func.count(Game.id)])
.where(
db.or_(
db.and_(Game.creator_id == cls.id,
Game.winner == 'creator'),
db.and_(Game.opponent_id == cls.id,
Game.winner == 'opponent'),
))
.label("cnt")
)
return db.case(
[(cnt == 0, 0)],
else_ = db.cast(won, db.Numeric) / cnt
)
# ...
q = session.query(Player).order_by(Player.winrate.desc())
这篇关于如何使用SQLAlchemy对* count *子查询求和?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!