def with_sql(self) -> str:
return """
crushes AS
(
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 crusher_id,
d.id AS crushee_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
),
crush_records AS (SELECT crusher_id, COUNT(crushee_id) AS crush_count, GROUP_CONCAT(crushee_id) AS crushee_ids FROM crushes GROUP BY crusher_id)
""".format(competition_ids_by_type_select=query.competition_ids_by_type_select('League'))
def active_competition_id_query():
return """
SELECT id FROM competition
WHERE
start_date < {now}
AND
end_date > {now}
AND
id IN ({competition_ids_by_type_select})
""".format(now=dtutil.dt2ts(dtutil.now()), competition_ids_by_type_select=query.competition_ids_by_type_select('League'))
def with_sql(self) -> str:
return """flawless_decks1 AS
(
SELECT
d.id AS 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(dm.games) = 10 AND SUM(odm.games) = 0
),
flawless_decks2 AS
(
SELECT
d.id AS 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(dm.games) = 10 AND SUM(odm.games) = 0
)
""".format(competition_ids_by_type_select=query.competition_ids_by_type_select('League'))
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_season(season_id: int = None, league_only: bool = False) -> Container:
season = Container()
where = 'TRUE'
if league_only:
where = 'd.competition_id IN ({competition_ids_by_type_select})'.format(competition_ids_by_type_select=query.competition_ids_by_type_select('League'))
season.decks = load_decks(where, season_id=season_id)
season.number = season_id
return season
def set_achievements(people):
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,
SUM(
CASE WHEN d.id IN
(
SELECT
d.id
FROM
deck AS d
{competition_join}
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
WHERE
ct.name = 'League'
GROUP BY
d.id
HAVING
SUM(CASE WHEN dm.games > odm.games THEN 1 ELSE 0 END) >= 5
AND
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) = 0
)
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
{competition_join}
WHERE
p.id IN ({ids})
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()))
results = [Container(r) for r in db().execute(sql)]
for result in results:
people_by_id[result['id']].update(result)
people_by_id[result['id']].achievements = len([k for k, v in result.items() if k != 'id' and v > 0])
