def leaderboards(where: str = "ct.name = 'Gatherling'",
season_id: Optional[int] = None) -> List[Dict[str, Any]]:
sql = """
SELECT
p.id AS person_id,
{person_query} AS person,
cs.name AS competition_series_name,
sp.name AS sponsor_name,
COUNT(DISTINCT d.id) AS tournaments,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins,
COUNT(DISTINCT d.id) + SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS points
FROM
competition AS c
INNER JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
sponsor AS sp ON sp.id = cs.sponsor_id
INNER JOIN
competition_type AS ct ON ct.id = cs.competition_type_id
INNER JOIN
deck AS d ON d.competition_id = c.id
INNER JOIN
person AS p ON d.person_id = p.id
LEFT JOIN
deck_match AS dm ON dm.deck_id = d.id
LEFT JOIN
deck_match AS odm ON odm.match_id = dm.match_id AND odm.deck_id <> d.id
{season_join}
WHERE
({where}) AND ({season_query})
GROUP BY
cs.id,
p.id
ORDER BY
cs.id,
points DESC,
wins DESC,
tournaments DESC,
person
""".format(person_query=query.person_query(),
season_join=query.season_join(),
where=where,
season_query=query.season_query(season_id))
results = []
current: Dict[str, Any] = {}
for row in db().select(sql):
k = row['competition_series_name']
if current.get('competition_series_name', None) != k:
if len(current) > 0:
results.append(current)
current = {
'competition_series_name': row['competition_series_name'],
'entries': [],
'sponsor_name': row['sponsor_name']
}
row.pop('competition_series_name')
current['entries'] = current['entries'] + [Container(row)]
if len(current) > 0:
results.append(current)
return results
def load_competitions(where: str = '1 = 1',
season_id: Optional[int] = None) -> List[Competition]:
sql = """
SELECT c.id, c.name, c.start_date, c.end_date, c.url,
COUNT(d.id) AS num_decks,
sp.name AS sponsor_name,
ct.name AS type
FROM competition AS c
LEFT JOIN deck AS d ON c.id = d.competition_id
LEFT JOIN competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN competition_type as ct ON ct.id = cs.competition_type_id
LEFT JOIN sponsor AS sp ON cs.sponsor_id = sp.id
{season_join}
WHERE ({where}) AND ({season_query})
GROUP BY c.id
ORDER BY c.start_date DESC, c.name
""".format(season_join=query.season_join(),
where=where,
season_query=query.season_query(season_id))
competitions = [Competition(r) for r in db().execute(sql)]
for c in competitions:
c.start_date = dtutil.ts2dt(c.start_date)
c.end_date = dtutil.ts2dt(c.end_date)
set_decks(competitions)
return competitions
def load_matchups(archetype_id, season_id=None):
sql = """
SELECT
oa.id,
oa.name,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS all_wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS all_losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS all_draws,
IFNULL(ROUND((SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN dm.games <> IFNULL(odm.games, 0) THEN 1 ELSE 0 END), 0)) * 100, 1), '') AS all_win_percent
FROM
archetype AS a
INNER JOIN
deck AS d ON d.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = a.id)
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
INNER JOIN
deck AS od ON od.id = odm.deck_id
INNER JOIN
archetype AS oa ON od.archetype_id IN (SELECT descendant FROM archetype_closure WHERE ancestor = oa.id)
{season_join}
WHERE
(a.id = %s) AND ({season_query})
GROUP BY
oa.id
ORDER BY
`all_wins` DESC, oa.name
""".format(season_join=query.season_join(),
season_query=query.season_query(season_id))
return [Container(m) for m in db().execute(sql, [archetype_id])]
def played_cards(where: str = '1 = 1',
season_id: Optional[int] = None) -> List[Card]:
sql = """
SELECT
card AS name,
{all_select},
{season_select}, -- We use the season data on the homepage to calculate movement, even though we no longer use it on /cards/.
{week_select}
FROM
deck_card AS dc
INNER JOIN
deck AS d ON dc.deck_id = d.id
{season_join}
{nwdl_join}
WHERE ({where}) AND ({season_query})
GROUP BY
dc.card
ORDER BY
all_num_decks DESC,
SUM(dsum.wins - dsum.losses) DESC,
name
""".format(all_select=deck.nwdl_all_select(),
season_select=deck.nwdl_season_select(),
week_select=deck.nwdl_week_select(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join(),
where=where,
season_query=query.season_query(season_id))
cs = [Container(r) for r in db().execute(sql)]
cards = oracle.cards_by_name()
for c in cs:
c.update(cards[c.name])
return cs
def load_head_to_head(
person_id: int,
where: str = 'TRUE',
order_by:
str = 'num_matches DESC, record DESC, win_percent DESC, wins DESC, opp_mtgo_username',
limit: str = '',
season_id: int = None) -> Sequence[Container]:
season_query = query.season_query(season_id)
sql = f"""
SELECT
hths.person_id AS id,
LOWER(opp.mtgo_username) AS opp_mtgo_username,
SUM(num_matches) AS num_matches,
SUM(wins) - SUM(losses) AS record,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) AS draws,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent
FROM
_head_to_head_stats AS hths
INNER JOIN
person AS opp ON hths.opponent_id = opp.id
WHERE
({where}) AND (hths.person_id = {person_id}) AND ({season_query})
GROUP BY
hths.person_id,
hths.opponent_id
ORDER BY
{order_by}
{limit}
"""
return [Container(r) for r in db().select(sql)]
def load_matches_count(where: str = 'TRUE',
season_id: Union[int, str, None] = None) -> int:
competition_join = query.competition_join()
season_join = query.season_join()
season_query = query.season_query(season_id, 'season.id')
sql = f"""
SELECT
COUNT(DISTINCT m.id)
FROM
`match` AS m
INNER JOIN
deck_match AS dm ON m.id = dm.match_id
INNER JOIN
deck AS d ON dm.deck_id = d.id
INNER JOIN
deck_cache AS dc ON d.id = dc.deck_id
INNER JOIN
person AS p ON d.person_id = p.id
LEFT JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND odm.deck_id <> d.id
LEFT JOIN
deck AS od ON odm.deck_id = od.id
LEFT JOIN
deck_cache AS odc ON od.id = odc.deck_id
LEFT JOIN
person AS o ON od.person_id = o.id
{competition_join}
{season_join}
WHERE
{where}
AND
{season_query}
"""
return int(db().value(sql))
def load_people_stats(
where: str,
season_id: Optional[int] = None) -> Dict[int, Dict[str, int]]:
season_join = query.season_join() if season_id else ''
sql = """
SELECT
p.id,
COUNT(d.id) AS num_decks,
SUM(dc.wins) AS wins,
SUM(dc.losses) AS losses,
SUM(dc.draws) AS draws,
SUM(CASE WHEN dc.wins >= 5 AND dc.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN d.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN d.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
IFNULL(ROUND((SUM(dc.wins) / NULLIF(SUM(dc.wins + dc.losses), 0)) * 100, 1), '') AS win_percent,
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS num_competitions
FROM
person AS p
LEFT JOIN
deck AS d ON d.person_id = p.id
LEFT JOIN
deck_cache AS dc ON d.id = dc.deck_id
{season_join}
WHERE
{where} AND {season_query}
GROUP BY
p.id
""".format(season_join=season_join,
where=where,
season_query=query.season_query(season_id, 'season.id'))
return {r['id']: r for r in db().select(sql)}
def only_played_by(person_id: int,
season_id: Optional[int] = None) -> List[Card]:
sql = """
SELECT
card AS name
FROM
deck_card AS dc
INNER JOIN
deck AS d ON dc.deck_id = d.id
{season_join}
WHERE
deck_id
IN
(
SELECT
DISTINCT deck_id
FROM
deck_match
) -- Only include cards that actually got played competitively rather than just posted to Goldfish as "new cards this season" or similar.
AND
({season_query})
GROUP BY
card
HAVING
COUNT(DISTINCT d.person_id) = 1
AND
MAX(d.person_id) = {person_id} -- In MySQL 5.7+ this could/should be ANY_VALUE not MAX but this works with any version. The COUNT(DISTINCT p.id) ensures this only has one possible value but MySQL can't work that out.-- In MySQL 5.7+ this could/should be ANY_VALUE not MAX but this works with any version. The COUNT(DISTINCT p.id) ensures this only has one possible value but MySQL can't work that out.
""".format(season_join=query.season_join(),
season_query=query.season_query(season_id),
person_id=sqlescape(person_id))
cards = {c.name: c for c in oracle.load_cards()}
return [cards[r['name']] for r in db().execute(sql)]
def played_cards_by_person(person_id: int, season_id: int) -> List[Card]:
sql = """
SELECT
name,
SUM(num_decks) AS num_decks,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) AS draws,
SUM(wins - losses) AS record,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent
FROM
_played_card_person_stats
WHERE
person_id = %s
AND
{season_query}
GROUP BY
name
HAVING
name IS NOT NULL
""".format(season_query=query.season_query(season_id))
print(sql)
cs = [Container(r) for r in decksite_db().select(sql, [person_id])]
print(len(cs))
cards = oracle.cards_by_name()
for c in cs:
c.update(cards[c.name])
return cs
def set_head_to_head(people: List[Person], season_id: int = None) -> None:
people_by_id = {person.id: person for person in people}
sql = """
SELECT
hths.person_id AS id,
LOWER(opp.mtgo_username) AS opp_mtgo_username,
SUM(num_matches) AS num_matches,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) AS draws,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent
FROM
_head_to_head_stats AS hths
INNER JOIN
person AS opp ON hths.opponent_id = opp.id
WHERE
hths.person_id IN ({ids}) AND ({season_query})
GROUP BY
hths.person_id,
hths.opponent_id
ORDER BY
SUM(num_matches) DESC,
SUM(wins - losses) DESC,
win_percent DESC,
SUM(wins) DESC,
opp_mtgo_username
""".format(ids=', '.join(str(k) for k in people_by_id.keys()),
season_query=query.season_query(season_id))
results = [Container(r) for r in db().select(sql)]
for result in results:
people_by_id[result.id].head_to_head = people_by_id[result.id].get(
'head_to_head', []) + [result]
for person in people:
if person.get('head_to_head') is None:
person.head_to_head = []
def load_cards(season_id: Optional[int] = None,
person_id: Optional[int] = None,
retry: bool = False) -> List[Card]:
if person_id:
table = '_card_person_stats'
where = 'person_id = {person_id}'.format(
person_id=sqlescape(person_id))
group_by = 'person_id, name'
else:
table = '_card_stats'
where = 'TRUE'
group_by = 'name'
sql = """
SELECT
name,
SUM(num_decks) AS num_decks,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) AS draws,
SUM(wins - losses) AS record,
SUM(num_decks_tournament) AS num_decks_tournament,
SUM(wins_tournament) AS wins_tournament,
SUM(losses_tournament) AS losses_tournament,
SUM(draws_tournament) AS draws_tournament,
SUM(wins_tournament - losses_tournament) AS record_tournament,
SUM(perfect_runs) AS perfect_runs,
SUM(tournament_wins) AS tournament_wins,
SUM(tournament_top8s) AS tournament_top8s,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent,
IFNULL(ROUND((SUM(wins_tournament) / NULLIF(SUM(wins_tournament + losses_tournament), 0)) * 100, 1), '') AS win_percent_tournament
FROM
{table} AS cs
WHERE
({where}) AND ({season_query})
GROUP BY
{group_by}
ORDER BY
num_decks DESC,
record,
name
""".format(table=table,
season_query=query.season_query(season_id),
where=where,
group_by=group_by)
try:
cs = [Container(r) for r in db().select(sql)]
cards = oracle.cards_by_name()
for c in cs:
c.update(cards[c.name])
return cs
except DatabaseException as e:
if not retry:
print(
f"Got {e} trying to load_cards so trying to preaggregate. If this is happening on user time that's undesirable."
)
preaggregate()
return load_cards(season_id, person_id, retry=True)
print(f'Failed to preaggregate. Giving up.')
raise e
def percent(self, season_id: Optional[int] = None) -> float:
season_condition = query.season_query(season_id)
sql = f'SELECT SUM(CASE WHEN {self.key} > 0 THEN 1 ELSE 0 END) AS pnum, COUNT(*) AS mnum FROM _achievements WHERE {season_condition}'
r = db().select(sql)[0]
try:
return int(r['pnum'] or 0) * 100.0 / int(r['mnum'])
except ZeroDivisionError:
return 0
def load_archetypes_deckless(
order_by: str = '`num_decks` DESC, `wins` DESC, name',
season_id: int = None,
retry: bool = False) -> List[Archetype]:
sql = """
SELECT
a.id,
a.name,
a.description,
aca.ancestor AS parent_id,
SUM(num_decks) AS num_decks,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) AS draws,
SUM(wins - losses) AS record,
SUM(num_decks_tournament) AS num_decks_tournament,
SUM(wins_tournament) AS wins_tournament,
SUM(losses_tournament) AS losses_tournament,
SUM(draws_tournament) AS draws_tournament,
SUM(wins_tournament - losses_tournament) AS record_tournament,
SUM(perfect_runs) AS perfect_runs,
SUM(tournament_wins) AS tournament_wins,
SUM(tournament_top8s) AS tournament_top8s,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent,
IFNULL(ROUND((SUM(wins_tournament) / NULLIF(SUM(wins_tournament + losses_tournament), 0)) * 100, 1), '') AS win_percent_tournament
FROM
archetype AS a
LEFT JOIN
_archetype_stats AS ars ON a.id = ars.archetype_id
LEFT JOIN
archetype_closure AS aca ON a.id = aca.descendant AND aca.depth = 1
WHERE
{season_query}
GROUP BY
a.id,
aca.ancestor -- aca.ancestor will be unique per a.id because of integrity constraints enforced elsewhere (each archetype has one ancestor) but we let the database know here.
ORDER BY
{order_by}
""".format(season_query=query.season_query(season_id), order_by=order_by)
try:
archetypes = [Archetype(a) for a in db().select(sql)]
archetypes_by_id = {a.id: a for a in archetypes}
for a in archetypes:
a.decks = []
a.decks_tournament = []
a.parent = archetypes_by_id.get(a.parent_id, None)
return archetypes
except DatabaseException as e:
if not retry:
print(
f"Got {e} trying to load_archetypes_deckless so trying to preaggregate. If this is happening on user time that's undesirable."
)
preaggregate_archetypes()
return load_archetypes_deckless(order_by=order_by,
season_id=season_id,
retry=True)
print(f'Failed to preaggregate. Giving up.')
raise e
def load_archetypes_deckless(order_by: Optional[str] = None,
person_id: Optional[int] = None,
season_id: Optional[int] = None,
tournament_only: bool = False) -> List[Archetype]:
if person_id:
table = '_arch_person_stats'
where = 'person_id = {person_id}'.format(
person_id=sqlescape(person_id))
group_by = 'ars.person_id, a.id'
else:
table = '_arch_stats'
where = 'TRUE'
group_by = 'a.id'
if tournament_only:
where = f"({where}) AND deck_type = 'tournament'"
sql = """
SELECT
a.id,
a.name,
a.description,
aca.ancestor AS parent_id,
SUM(num_decks) AS num_decks,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) AS draws,
SUM(wins - losses) AS record,
SUM(perfect_runs) AS perfect_runs,
SUM(tournament_wins) AS tournament_wins,
SUM(tournament_top8s) AS tournament_top8s,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent
FROM
archetype AS a
LEFT JOIN
{table} AS ars ON a.id = ars.archetype_id
LEFT JOIN
archetype_closure AS aca ON a.id = aca.descendant AND aca.depth = 1
WHERE
({where}) AND ({season_query})
GROUP BY
{group_by},
aca.ancestor -- aca.ancestor will be unique per a.id because of integrity constraints enforced elsewhere (each archetype has one ancestor) but we let the database know here.
ORDER BY
{order_by}
""".format(table=table,
where=where,
group_by=group_by,
season_query=query.season_query(season_id),
order_by=order_by or 'TRUE')
archetypes = [Archetype(a) for a in db().select(sql)]
archetypes_by_id = {a.id: a for a in archetypes}
for a in archetypes:
a.decks = []
a.decks_tournament = []
a.parent = archetypes_by_id.get(a.parent_id, None)
if order_by is None:
archetypes = preorder(archetypes)
return archetypes
def load_people(where: str = '1 = 1',
order_by: str = '`all_num_decks` DESC, name',
season_id: Optional[int] = None) -> Sequence[Person]:
sql = """
SELECT
p.id,
{person_query} AS name,
p.mtgo_username,
p.tappedout_username,
p.mtggoldfish_username,
p.discord_id,
p.elo,
{all_select},
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL AND {season_query} THEN 1 ELSE 0 END) AS `all_num_competitions`
FROM
person AS p
LEFT JOIN
deck AS d ON p.id = d.person_id
{season_join}
{nwdl_join}
WHERE
({where})
GROUP BY
p.id
ORDER BY
{order_by}
""".format(person_query=query.person_query(),
all_select=deck.nwdl_select('all_',
query.season_query(season_id)),
nwdl_join=deck.nwdl_join(),
season_join=query.season_join(),
where=where,
season_query=query.season_query(season_id),
order_by=order_by)
people = [Person(r) for r in db().select(sql)]
for p in people:
p.season_id = season_id
if len(people) > 0:
set_achievements(people, season_id)
set_head_to_head(people, season_id)
return people
def leaderboard(
self,
season_id: Optional[int] = None) -> Optional[List[Container]]:
season_condition = query.season_query(season_id)
person_query = query.person_query()
sql = f"""
SELECT
{person_query} AS person,
SUM({self.key}) AS points,
p.id AS person_id
FROM
person AS p
JOIN
_achievements
ON
p.id = _achievements.person_id
WHERE
{season_condition}
GROUP BY
p.id
HAVING
points >=
( -- Work out the minimum score to make top N, counting ties
SELECT
MIN(s)
FROM
(
SELECT
SUM({self.key}) AS s
FROM
_achievements
WHERE
{season_condition}
GROUP BY
person_id
HAVING
s > 0
ORDER BY
s DESC
LIMIT
{LEADERBOARD_TOP_N}
) AS _
)
ORDER BY
points DESC,
name
LIMIT {LEADERBOARD_LIMIT}
"""
leaderboard = []
for row in db().select(sql):
c = Container(row)
c.score = c.points
leaderboard.append(c)
return leaderboard if len(leaderboard) > 0 else None
def load_summary(self, season_id: Optional[int] = None) -> Optional[str]:
season_condition = query.season_query(season_id)
sql = f'SELECT SUM(`{self.key}`) AS num, COUNT(DISTINCT person_id) AS pnum FROM _achievements WHERE `{self.key}` > 0 AND {season_condition}'
for r in db().select(sql):
res = Container(r)
if res.num is None:
return 'Not earned by any players.'
times_text = ngettext(' once', ' %(num)d times', res.num) if res.num > res.pnum else ''
players_text = ngettext('1 player', '%(num)d players', res.pnum)
return f'Earned{times_text} by {players_text}.'
return None
def load_cards(
additional_where: str = 'TRUE',
order_by: str = 'num_decks DESC, record, name',
limit: str = '',
person_id: Optional[int] = None,
season_id: Optional[int] = None,
tournament_only: bool = False,
) -> List[Card]:
if person_id:
table = '_card_person_stats'
where = 'person_id = {person_id}'.format(
person_id=sqlescape(person_id))
group_by = 'person_id, name'
else:
table = '_card_stats'
where = 'TRUE'
group_by = 'name'
if tournament_only:
where = f"({where}) AND deck_type = 'tournament'"
sql = """
SELECT
name,
SUM(num_decks) AS num_decks,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) AS draws,
SUM(wins - losses) AS record,
SUM(perfect_runs) AS perfect_runs,
SUM(tournament_wins) AS tournament_wins,
SUM(tournament_top8s) AS tournament_top8s,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent
FROM
{table} AS cs
WHERE
({where}) AND ({additional_where}) AND ({season_query})
GROUP BY
{group_by}
ORDER BY
{order_by}
{limit}
""".format(table=table,
season_query=query.season_query(season_id),
where=where,
additional_where=additional_where,
group_by=group_by,
order_by=order_by,
limit=limit)
cs = [Container(r) for r in db().select(sql)]
cards = oracle.cards_by_name()
for c in cs:
c.update(cards[c.name])
return cs
def load_cards_count(additional_where: str = 'TRUE',
person_id: Optional[int] = None,
season_id: Optional[int] = None) -> int:
if person_id:
table = '_card_person_stats'
where = 'person_id = {person_id}'.format(
person_id=sqlescape(person_id))
else:
table = '_card_stats'
where = 'TRUE'
season_query = query.season_query(season_id)
sql = f'SELECT COUNT(DISTINCT name) AS n FROM {table} WHERE ({where}) AND ({additional_where}) AND ({season_query})'
return int(db().value(sql))
def load_competitions(
where: str = 'TRUE',
having: str = 'TRUE',
season_id: Optional[int] = None,
should_load_decks: Optional[bool] = False) -> List[Competition]:
sql = """
SELECT
c.id,
c.name,
c.start_date,
c.end_date,
c.url,
c.top_n,
COUNT(d.id) AS num_decks,
SUM(CASE WHEN d.reviewed THEN 1 ELSE 0 END) AS num_reviewed,
sp.name AS sponsor_name,
cs.name AS series_name,
ct.name AS type,
season.id AS season_id
FROM
competition AS c
LEFT JOIN
deck AS d ON c.id = d.competition_id
LEFT JOIN
competition_series AS cs ON cs.id = c.competition_series_id
LEFT JOIN
competition_type as ct ON ct.id = cs.competition_type_id
LEFT JOIN
sponsor AS sp ON cs.sponsor_id = sp.id
{season_join}
WHERE
({where}) AND ({season_query})
GROUP BY
c.id
HAVING
{having}
ORDER BY
c.start_date DESC,
c.name
""".format(season_join=query.season_join(),
where=where,
season_query=query.season_query(season_id, 'season.id'),
having=having)
competitions = [Competition(r) for r in db().select(sql)]
for c in competitions:
c.start_date = dtutil.ts2dt(c.start_date)
c.end_date = dtutil.ts2dt(c.end_date)
c.decks = []
if should_load_decks:
load_decks(competitions)
return competitions
def load_people(
where: str = 'TRUE',
order_by: str = 'num_decks DESC, p.name',
limit: str = '',
season_id: Optional[Union[str, int]] = None) -> Sequence[Person]:
person_query = query.person_query()
season_join = query.season_join() if season_id else ''
season_query = query.season_query(season_id, 'season.id')
sql = f"""
SELECT
p.id,
{person_query} AS name,
p.mtgo_username,
p.tappedout_username,
p.mtggoldfish_username,
p.discord_id,
p.elo,
p.locale,
SUM(1) AS num_decks,
SUM(dc.wins) AS wins,
SUM(dc.losses) AS losses,
SUM(dc.draws) AS draws,
SUM(wins - losses) AS record,
SUM(CASE WHEN dc.wins >= 5 AND dc.losses = 0 AND d.source_id IN (SELECT id FROM source WHERE name = 'League') THEN 1 ELSE 0 END) AS perfect_runs,
SUM(CASE WHEN d.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN d.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
IFNULL(ROUND((SUM(dc.wins) / NULLIF(SUM(dc.wins + dc.losses), 0)) * 100, 1), '') AS win_percent,
SUM(DISTINCT CASE WHEN d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS num_competitions
FROM
person AS p
LEFT JOIN
deck AS d ON d.person_id = p.id
LEFT JOIN
deck_cache AS dc ON d.id = dc.deck_id
{season_join}
WHERE
({where}) AND ({season_query})
GROUP BY
p.id
ORDER BY
{order_by}
{limit}
"""
people = [Person(r) for r in db().select(sql)]
for p in people:
p.season_id = season_id
return people
def set_head_to_head(people: List[Person], season_id: int = None) -> None:
people_by_id = {person.id: person for person in people}
sql = """
SELECT
p.id,
COUNT(p.id) AS num_matches,
LOWER(opp.mtgo_username) AS opp_mtgo_username,
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws,
IFNULL(ROUND((SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN dm.games <> IFNULL(odm.games, 0) THEN 1 ELSE 0 END), 0)) * 100, 1), '') AS win_percent
FROM
person AS p
INNER JOIN
deck AS d ON p.id = d.person_id
INNER JOIN
deck_match AS dm ON dm.deck_id = d.id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> IFNULL(odm.deck_id, 0)
INNER JOIN
deck AS od ON odm.deck_id = od.id
INNER JOIN
person AS opp ON od.person_id = opp.id
{season_join}
WHERE
p.id IN ({ids}) AND ({season_query})
GROUP BY
p.id, opp.id
ORDER BY
p.id,
num_matches DESC,
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) - SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) DESC,
win_percent DESC,
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) DESC,
opp_mtgo_username
""".format(ids=', '.join(str(k) for k in people_by_id.keys()),
season_join=query.season_join(),
season_query=query.season_query(season_id))
results = [Container(r) for r in db().select(sql)]
for result in results:
people_by_id[result.id].head_to_head = people_by_id[result.id].get(
'head_to_head', []) + [result]
for person in people:
if person.get('head_to_head') is None:
person.head_to_head = []
def load_all_matchups(where: str = 'TRUE',
season_id: Optional[int] = None,
retry: bool = False) -> List[Container]:
sql = """
SELECT
archetype_id,
a.name AS archetype_name,
opponent_archetype_id AS id,
oa.name AS name,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) AS draws,
SUM(wins_tournament) AS wins_tournament,
SUM(losses_tournament) AS losses_tournament,
SUM(draws_tournament) AS draws_tournament,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent,
IFNULL(ROUND((SUM(wins_tournament) / NULLIF(SUM(wins_tournament + losses_tournament), 0)) * 100, 1), '') AS win_percent_tournament
FROM
_matchup_stats AS ms
INNER JOIN
archetype AS a ON archetype_id = a.id
INNER JOIN
archetype AS oa ON opponent_archetype_id = oa.id
WHERE
({where}) AND ({season_query})
GROUP BY
archetype_id,
opponent_archetype_id
ORDER BY
wins DESC,
oa.name
""".format(where=where, season_query=query.season_query(season_id))
try:
return [Container(m) for m in db().select(sql)]
except DatabaseException as e:
if not retry:
print(
f"Got {e} trying to load_all_matchups so trying to preaggregate. If this is happening on user time that's undesirable."
)
preaggregate_matchups()
return load_all_matchups(where=where,
season_id=season_id,
retry=True)
print(f'Failed to preaggregate. Giving up.')
raise e
def load_query(people_by_id: Dict[int, Person],
season_id: Optional[int]) -> str:
# keys have been normalised earlier but could still be reserved words
columns = ', '.join(f'SUM(`{a.key}`) as `{a.key}`'
for a in Achievement.all_achievements if a.in_db)
return """
SELECT
person_id AS id,
{columns}
FROM
_achievements AS a
WHERE
person_id IN ({ids}) AND ({season_query})
GROUP BY
person_id
""".format(columns=columns,
ids=', '.join(str(k) for k in people_by_id.keys()),
season_query=query.season_query(season_id))
def load_people_count(where: str = 'TRUE',
season_id: Optional[Union[str, int]] = None) -> int:
season_join = query.season_join() if season_id else ''
season_query = query.season_query(season_id, 'season.id')
sql = f"""
SELECT
COUNT(DISTINCT p.id)
FROM
person AS p
LEFT JOIN
deck AS d ON d.person_id = p.id
LEFT JOIN
deck_cache AS dc ON d.id = dc.deck_id
{season_join}
WHERE
({where}) AND ({season_query})
"""
return db().value(sql) or 0
def detail(self, p: Person, season_id: Optional[int] = None) -> Optional[List[Deck]]:
if self.detail_sql is None:
return None
sql = """
SELECT
GROUP_CONCAT({k}_detail)
FROM
_achievements
WHERE
person_id={id} AND {season_query}
GROUP BY
person_id
""".format(k=self.key, id=p.id, season_query=query.season_query(season_id))
ids = db().value(sql)
result = Container()
result.decks = deck.load_decks(where=f'd.id IN ({ids})') if ids is not None else []
for f in self.flags:
result[f] = True
return result
def load_matchups(where: str = 'TRUE',
archetype_id: Optional[int] = None,
person_id: Optional[int] = None,
season_id: Optional[int] = None,
tournament_only: bool = False) -> List[Container]:
if person_id:
table = '_matchup_ps_stats'
where = f'({where}) AND (mps.person_id = {person_id})'
else:
table = '_matchup_stats'
if archetype_id:
where = f'({where}) AND (a.id = {archetype_id})'
if tournament_only:
where = f"({where}) AND (mps.deck_type = 'tournament')"
sql = """
SELECT
archetype_id,
a.name AS archetype_name,
opponent_archetype_id AS id,
oa.name AS name,
SUM(wins) AS wins,
SUM(losses) AS losses,
SUM(draws) AS draws,
IFNULL(ROUND((SUM(wins) / NULLIF(SUM(wins + losses), 0)) * 100, 1), '') AS win_percent
FROM
{table} AS mps
INNER JOIN
archetype AS a ON archetype_id = a.id
INNER JOIN
archetype AS oa ON opponent_archetype_id = oa.id
WHERE
({where}) AND ({season_query})
GROUP BY
archetype_id,
opponent_archetype_id
ORDER BY
wins DESC,
oa.name
""".format(table=table,
where=where,
season_query=query.season_query(season_id))
return [Container(m) for m in db().select(sql)]
def load_archetypes_deckless(
where: str = '1 = 1',
order_by: str = '`all_num_decks` DESC, `all_wins` DESC, name',
season_id: int = None) -> List[Archetype]:
sql = """
SELECT
a.id,
a.name,
aca.ancestor AS parent_id,
{all_select}
FROM
archetype AS a
LEFT JOIN
archetype_closure AS aca ON a.id = aca.descendant AND aca.depth = 1
LEFT JOIN
archetype_closure AS acd ON a.id = acd.ancestor
LEFT JOIN
deck AS d ON acd.descendant = d.archetype_id
{season_join}
{nwdl_join}
WHERE ({where}) AND ({season_query})
GROUP BY
a.id,
aca.ancestor -- aca.ancestor will be unique per a.id because of integrity constraints enforced elsewhere (each archetype has one ancestor) but we let the database know here.
ORDER BY
{order_by}
""".format(all_select=deck.nwdl_all_select(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join(),
where=where,
season_query=query.season_query(season_id),
order_by=order_by)
archetypes = [Archetype(a) for a in db().execute(sql)]
archetypes_by_id = {a.id: a for a in archetypes}
for a in archetypes:
a.decks = []
a.parent = archetypes_by_id.get(a.parent_id, None)
return archetypes
def set_achievements(people: List[Person], season_id: int = None) -> None:
people_by_id = {person.id: person for person in people}
sql = """
SELECT
p.id,
COUNT(DISTINCT CASE WHEN ct.name = 'Gatherling' THEN d.id ELSE NULL END) AS tournament_entries,
COUNT(DISTINCT CASE WHEN d.finish = 1 AND ct.name = 'Gatherling' THEN d.id ELSE NULL END) AS tournament_wins,
COUNT(DISTINCT CASE WHEN ct.name = 'League' THEN d.id ELSE NULL END) AS league_entries,
CASE WHEN COUNT(CASE WHEN d.retired = 1 THEN 1 ELSE NULL END) = 0 THEN True ELSE False END AS completionist,
SUM(CASE WHEN ct.name = 'League' AND dc.wins >= 5 AND dc.losses THEN 1 ELSE 0 END) AS perfect_runs,
SUM(
CASE WHEN d.id IN
(
SELECT
-- MAX here is just to fool MySQL to give us the id of the deck that crushed the perfect run from an aggregate function. There is only one value to MAX.
MAX(CASE WHEN dm.games < odm.games AND dm.match_id IN (SELECT MAX(match_id) FROM deck_match WHERE deck_id = d.id) THEN odm.deck_id ELSE NULL END) AS deck_id
FROM
deck AS d
INNER JOIN
deck_match AS dm
ON
dm.deck_id = d.id
INNER JOIN
deck_match AS odm
ON
dm.match_id = odm.match_id AND odm.deck_id <> d.id
WHERE
d.competition_id IN ({competition_ids_by_type_select})
GROUP BY d.id
HAVING
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) >=4
AND
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) = 1
AND
SUM(CASE WHEN dm.games < odm.games AND dm.match_id IN (SELECT MAX(match_id) FROM deck_match WHERE deck_id = d.id) THEN 1 ELSE 0 END) = 1
)
THEN 1 ELSE 0 END
) AS perfect_run_crushes
FROM
person AS p
LEFT JOIN
deck AS d ON d.person_id = p.id
LEFT JOIN
deck_cache AS dc ON dc.deck_id = d.id
{season_join}
{competition_join}
WHERE
p.id IN ({ids}) AND ({season_query})
GROUP BY
p.id
""".format(
competition_join=query.competition_join(),
competition_ids_by_type_select=query.competition_ids_by_type_select(
'League'),
ids=', '.join(str(k) for k in people_by_id.keys()),
season_join=query.season_join(),
season_query=query.season_query(season_id))
results = [Container(r) for r in db().select(sql)]
for result in results:
people_by_id[result['id']].num_achievements = len(
[k for k, v in result.items() if k != 'id' and v > 0])
people_by_id[result['id']].achievements = result
people_by_id[result['id']].achievements.pop('id')
def load_decks(where: str = '1 = 1',
having: str = '1 = 1',
order_by: Optional[str] = None,
limit: str = '',
season_id: Optional[int] = None
) -> List[Deck]:
if not redis.enabled():
return load_decks_heavy(where, having, order_by, limit, season_id)
if order_by is None:
order_by = 'active_date DESC, d.finish IS NULL, d.finish'
sql = """
SELECT
d.id,
d.finish,
d.decklist_hash,
cache.active_date,
cache.wins,
cache.losses,
cache.draws,
ct.name AS competition_type_name
FROM
deck AS d
"""
if 'p.' in where or 'p.' in order_by:
sql += """
LEFT JOIN
person AS p ON d.person_id = p.id
"""
if 's.' in where or 's.' in order_by:
sql += """
LEFT JOIN
source AS s ON d.source_id = s.id
"""
if 'a.' in where or 'a.' in order_by:
sql += """
LEFT JOIN
archetype AS a ON d.archetype_id = a.id
"""
sql += """
{competition_join}
LEFT JOIN
deck_cache AS cache ON d.id = cache.deck_id
{season_join}
WHERE
({where}) AND ({season_query})
GROUP BY
d.id,
d.competition_id, -- Every deck has only one competition_id but if we want to use competition_id in the HAVING clause we need this.
season.id -- In theory this is not necessary as all decks are in a single season and we join on the date but MySQL cannot work that out so give it the hint it needs.
HAVING
{having}
ORDER BY
{order_by}
{limit}
"""
sql = sql.format(person_query=query.person_query(), competition_join=query.competition_join(), season_query=query.season_query(season_id, 'season.id'), season_join=query.season_join(), where=where, having=having, order_by=order_by, limit=limit)
db().execute('SET group_concat_max_len=100000')
rows = db().select(sql)
decks_by_id = {}
heavy = []
for row in rows:
d = redis.get_container('decksite:deck:{id}'.format(id=row['id']))
if d is None or d.name is None:
heavy.append(row['id'])
else:
decks_by_id[row['id']] = deserialize_deck(d)
if heavy:
where = 'd.id IN ({deck_ids})'.format(deck_ids=', '.join(map(sqlescape, map(str, heavy))))
loaded_decks = load_decks_heavy(where)
for d in loaded_decks:
decks_by_id[d.id] = d
decks = []
for row in rows:
decks.append(decks_by_id[row['id']])
return decks