sfmagic.org Rewrite: Head-to-Head Done
Unless I’m forgetting about something important, head-to-head is done.
The queries I have to support are:
Get a list of all a player’s opponents.
Get a list of all a player’s matches with a given opponent.
Get the match record between two players.
Get the game record between two players.
Yesterday I created the game record. Tonight I managed the other three.
List all opponents:
def opponentlist(self, player1id):
l1 = conn.execute(
select(
[h2hresults_table.c.player2], h2hresults_table.c.player1==player1id
).distinct()
).fetchall()
l2 = conn.execute(
select(
[h2hresults_table.c.player1], h2hresults_table.c.player2==player1id
).distinct()
).fetchall()
l = []
for each in l1:
if each['player2'] not in l:
l.append(each['player2'])
for each in l2:
if each['player1'] not in l:
l.append(each['player1'])
return l
Not very elegant, but I couldn’t figure out how to get pure SQL (or SQLAlchemy) to give me a single list of the player IDs.
List all matches between two players:
def matchlist(self, player1id, player2id):
call = select(
[h2hresults_table],
or_(
and_(
h2hresults_table.c.player1==player1id,
h2hresults_table.c.player2==player2id
),
and_(h2hresults_table.c.player1==player2id,
h2hresults_table.c.player2==player1id
)
)
).order_by(h2hresults_table.c.tournament_id)
return conn.execute(call).fetchall()
That was my first use of the or_ and and_ operators from SQLAlchemy, and they feel slightly odd… perhaps because the raw SQL would have the AND operators on the outside, with the OR operator in the middle—WHERE (player1=player1id AND player2=player2id) OR (player1=player2id AND player2=player1id)—rather than the other way around.
Match record between two players:
def matchrecord(self, player1id, player2id):
call = select([
select([func.count(h2hresults_table.c.wins)], h2hresults_table.c.player1==player1id)
.where(h2hresults_table.c.player2==player2id)
.where(h2hresults_table.c.wins>h2hresults_table.c.losses)
.label('wins'),
select([func.count(h2hresults_table.c.wins)], h2hresults_table.c.player1==player2id)
.where(h2hresults_table.c.player2==player1id)
.where(h2hresults_table.c.wins>h2hresults_table.c.losses)
.label('losses'),
select([func.count(h2hresults_table.c.wins)],
or_(
and_(
h2hresults_table.c.player1==player1id,
h2hresults_table.c.player2==player2id
),
and_(h2hresults_table.c.player1==player2id,
h2hresults_table.c.player2==player1id
)
)
)
.where(h2hresults_table.c.wins==h2hresults_table.c.losses)
.label('draws')
])
return conn.execute(call).fetchone()
Like the game record query from yesterday, the match record query took me a while to translate from SQL to SQLAlchemy.
The head-to-head queries are done. I haven’t done a head-to-head page yet, which would be fairly trivial given that the queries are done. It would also be fairly pointless since there’s no real head-to-head data in the system, and there won’t be until I write the new entry system and then enter the backlog of results. The framework to support that entry, and the storage and retrieval of head-to-head statistics, is there, and that marks a major new feature.
Next: entering the results. I don’t know if I can get that done in the next two days, but it’s the lone remaining major task.