sfmagic.org Rewrite: Head-to-Head Some Working SQLAlchemy
.I haven’t had much time to work on the project over the weekend, but I have made some progress in getting the queries into SQLAlchemy form.
Having found no further documentation on IF statements, I decided that I should probably rewrite the SQL itself using another approach, so I tried subqueries:
select
sum(a.wins + b.wins), sum(a.losses + b.losses), sum(a.draws + b.draws)
from
(select
sum(wins) as wins, sum(losses) as losses, sum(draws) as draws
from h2hresults
where
player1=99 and player2=4
) as a,
(select
sum(losses) as wins, sum(wins) as losses, sum(draws) as draws
from h2hresults
where
player1=4 and player2=99
) as b;
That returns the game record between players 4 and 99. It took me quite a while to get to the SQLAlchemy for for that, partly because of the use of label() or as_scalar(). I’m still fairly sure that this version isn’t quite right:
select([
func.sum(
select([
func.sum(h2hresults_table.c.wins)], h2hresults_table.c.player1==player1id)
.where(h2hresults_table.c.player2==player2id).as_scalar()
+
select([
func.sum(h2hresults_table.c.losses)], h2hresults_table.c.player1==player2id)
.where(h2hresults_table.c.player2==player1id).as_scalar()
).label('wins'),
func.sum(
select([
func.sum(h2hresults_table.c.losses)], h2hresults_table.c.player1==player1id)
.where(h2hresults_table.c.player2==player2id).as_scalar()
+
select([
func.sum(h2hresults_table.c.wins)], h2hresults_table.c.player1==player2id)
.where(h2hresults_table.c.player2==player1id).as_scalar()
).label('losses'),
func.sum(
select([
func.sum(h2hresults_table.c.draws)], h2hresults_table.c.player1==player1id)
.where(h2hresults_table.c.player2==player2id).as_scalar()
+
select([
func.sum(h2hresults_table.c.draws)], h2hresults_table.c.player1==player2id)
.where(h2hresults_table.c.player2==player1id).as_scalar()
).label('draws'),
])
It works, but the SQL it actually sends to the server looks something like this:
SELECT sum(
(SELECT sum(h2hresults.wins)
FROM h2hresults
WHERE h2hresults.player1 = :h2hresults_player1_1 AND h2hresults.player2 = :h2hresults_player2_1)
+
(SELECT sum(h2hresults.losses)
FROM h2hresults
WHERE h2hresults.player1 = :h2hresults_player1_2 AND h2hresults.player2 = :h2hresults_player2_2)
) AS wins,
sum(
(SELECT sum(h2hresults.losses)
FROM h2hresults
WHERE h2hresults.player1 = :h2hresults_player1_3 AND h2hresults.player2 = :h2hresults_player2_3)
+
(SELECT sum(h2hresults.wins)
FROM h2hresults
WHERE h2hresults.player1 = :h2hresults_player1_4 AND h2hresults.player2 = :h2hresults_player2_4)
) AS losses,
sum(
(SELECT sum(h2hresults.draws)
FROM h2hresults
WHERE h2hresults.player1 = :h2hresults_player1_5 AND h2hresults.player2 = :h2hresults_player2_5)
+
(SELECT sum(h2hresults.draws)
FROM h2hresults
WHERE h2hresults.player1 = :h2hresults_player1_6 AND h2hresults.player2 = :h2hresults_player2_6)
) AS draws
That, clearly, is not the same as the SQL I started out with, although perhaps it’s more logical and straightforward as an approach. Hopefully the other queries (match record between two players, and who player X has the most match wins against) won’t be too tough, and I’ll be done with head-to-head shortly.