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 played_cards(where='1 = 1'):
sql = """
SELECT
card AS name,
{all_select},
{season_select},
{week_select}
FROM
deck_card AS dc
INNER JOIN
deck AS d ON dc.deck_id = d.id
{nwdl_join}
WHERE
{where}
GROUP BY
dc.card
ORDER BY
season_num_decks DESC,
SUM(CASE WHEN dsum.created_date >= %s THEN dsum.wins - dsum.losses ELSE 0 END) DESC,
name
""".format(all_select=deck.nwdl_all_select(),
season_select=deck.nwdl_season_select(),
week_select=deck.nwdl_week_select(),
nwdl_join=deck.nwdl_join(),
where=where)
cs = [
Container(r)
for r in db().execute(sql, [int(rotation.last_rotation().timestamp())])
]
cards = oracle.cards_by_name()
for c in cs:
c.update(cards[c.name])
return cs
def preaggregate_card() -> None:
db().execute('DROP TABLE IF EXISTS _new_card_stats')
db().execute("""
CREATE TABLE IF NOT EXISTS _new_card_stats (
name VARCHAR(190) NOT NULL,
`day` INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
PRIMARY KEY (`day`, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
UNIX_TIMESTAMP(DATE(FROM_UNIXTIME(d.created_date))) AS `day`,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(wins), 0) AS wins,
IFNULL(SUM(losses), 0) AS losses,
IFNULL(SUM(draws), 0) AS draws,
SUM(CASE WHEN wins >= 5 AND 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 dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s
FROM
deck AS d
INNER JOIN
deck_card AS dc ON d.id = dc.deck_id
{nwdl_join}
GROUP BY
card,
`day`
""".format(nwdl_join=deck.nwdl_join()))
db().execute('DROP TABLE IF EXISTS _card_stats')
db().execute('RENAME TABLE _new_card_stats TO _card_stats')
def preaggregate_archetype_person() -> None:
# This preaggregation fails if I use the obvious name _archetype_person_stats but works with any other name. It's confusing.
table = '_arch_person_stats'
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
archetype_id INT NOT NULL,
person_id INT NOT NULL,
season_id INT NOT NULL,
num_decks INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (person_id, season_id, archetype_id, deck_type),
FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
d.person_id,
season.id AS season_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(wins), 0) AS wins,
IFNULL(SUM(losses), 0) AS losses,
IFNULL(SUM(draws), 0) AS draws,
SUM(CASE WHEN wins >= 5 AND 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 dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
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
{competition_join}
{season_join}
{nwdl_join}
GROUP BY
a.id,
d.person_id,
season.id,
ct.name
HAVING
season.id IS NOT NULL
""".format(table=table,
competition_join=query.competition_join(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join())
preaggregation.preaggregate(table, sql)
def preaggregate_card_person() -> None:
db().execute('DROP TABLE IF EXISTS _new_card_person_stats')
db().execute("""
CREATE TABLE IF NOT EXISTS _new_card_person_stats (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
person_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
wins_tournament INT NOT NULL,
losses_tournament INT NOT NULL,
draws_tournament INT NOT NULL,
PRIMARY KEY (season_id, person_id, name),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_person_id_name (person_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.id AS season_id,
d.person_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(wins), 0) AS wins,
IFNULL(SUM(losses), 0) AS losses,
IFNULL(SUM(draws), 0) AS draws,
SUM(CASE WHEN wins >= 5 AND 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 dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
SUM(CASE WHEN (d.id IS NOT NULL) AND (ct.name = 'Gatherling') THEN 1 ELSE 0 END) AS num_decks_tournament,
IFNULL(SUM(CASE WHEN ct.name = 'Gatherling' THEN wins ELSE 0 END), 0) AS wins_tournament,
IFNULL(SUM(CASE WHEN ct.name = 'Gatherling' THEN losses ELSE 0 END), 0) AS losses_tournament,
IFNULL(SUM(CASE WHEN ct.name = 'Gatherling' THEN draws ELSE 0 END), 0) AS draws_tournament
FROM
deck AS d
INNER JOIN
deck_card AS dc ON d.id = dc.deck_id
{competition_join}
{season_join}
{nwdl_join}
GROUP BY
card,
d.person_id,
season.id
""".format(competition_join=query.competition_join(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join()))
db().execute('DROP TABLE IF EXISTS _old_card_person_stats')
db().execute('CREATE TABLE IF NOT EXISTS _card_person_stats (_ INT)'
) # Prevent error in RENAME TABLE below if bootstrapping.
db().execute(
'RENAME TABLE _card_person_stats TO _old_card_person_stats, _new_card_person_stats TO _card_person_stats'
)
db().execute('DROP TABLE IF EXISTS _old_card_person_stats')
def preaggregate_archetypes() -> None:
table = '_arch_stats'
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
archetype_id INT NOT NULL,
season_id INT NOT NULL,
num_decks INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, archetype_id, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
season.id AS season_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(wins), 0) AS wins,
IFNULL(SUM(losses), 0) AS losses,
IFNULL(SUM(draws), 0) AS draws,
SUM(CASE WHEN wins >= 5 AND 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 dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
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
{competition_join}
{season_join}
{nwdl_join}
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.
season.id,
ct.name
HAVING
season.id IS NOT NULL
""".format(table=table,
competition_join=query.competition_join(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join())
preaggregation.preaggregate(table, sql)
def preaggregate_card_person() -> None:
table = '_card_person_stats'
sql = """
CREATE TABLE IF NOT EXISTS _new{table} (
name VARCHAR(190) NOT NULL,
season_id INT NOT NULL,
person_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
deck_type ENUM('league', 'tournament', 'other') NOT NULL,
PRIMARY KEY (season_id, person_id, name, deck_type),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (person_id) REFERENCES person (id) ON UPDATE CASCADE ON DELETE CASCADE,
INDEX idx_person_id_name (person_id, name)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
card AS name,
season.id AS season_id,
d.person_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(wins), 0) AS wins,
IFNULL(SUM(losses), 0) AS losses,
IFNULL(SUM(draws), 0) AS draws,
SUM(CASE WHEN wins >= 5 AND 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 dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
(CASE WHEN ct.name = 'League' THEN 'league' WHEN ct.name = 'Gatherling' THEN 'tournament' ELSE 'other' END) AS deck_type
FROM
deck AS d
INNER JOIN
-- Eiliminate maindeck/sideboard double-counting with DISTINCT. See #5493.
(SELECT DISTINCT card, deck_id FROM deck_card) AS dc ON d.id = dc.deck_id
{competition_join}
{season_join}
{nwdl_join}
GROUP BY
card,
d.person_id,
season.id,
ct.name
""".format(table=table,
competition_join=query.competition_join(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join())
preaggregation.preaggregate(table, sql)
def preaggregate_archetypes() -> None:
db().execute('DROP TABLE IF EXISTS _new_archetype_stats')
sql = """
CREATE TABLE IF NOT EXISTS _new_archetype_stats (
archetype_id INT NOT NULL,
`day` INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
PRIMARY KEY (`day`, archetype_id),
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
UNIX_TIMESTAMP(DATE(FROM_UNIXTIME(d.created_date))) AS `day`,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(wins), 0) AS wins,
IFNULL(SUM(losses), 0) AS losses,
IFNULL(SUM(draws), 0) AS draws,
SUM(CASE WHEN wins >= 5 AND 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 dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s
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
{nwdl_join}
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.
`day`
HAVING
`day` IS NOT NULL
""".format(nwdl_join=deck.nwdl_join())
db().execute(sql)
db().execute('DROP TABLE IF EXISTS _archetype_stats')
db().execute('RENAME TABLE _new_archetype_stats TO _archetype_stats')
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 load_matchups(archetype_id):
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,
SUM(CASE WHEN d.created_date > %s AND dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS season_wins, -- IFNULL so we still count byes as wins.
SUM(CASE WHEN d.created_date > %s AND dm.games < odm.games THEN 1 ELSE 0 END) AS season_losses,
SUM(CASE WHEN d.created_date > %s AND dm.games = odm.games THEN 1 ELSE 0 END) AS season_draws,
IFNULL(ROUND((SUM(CASE WHEN d.created_date > %s AND dm.games > odm.games THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN d.created_date > %s AND dm.games <> IFNULL(odm.games, 0) THEN 1 ELSE 0 END), 0)) * 100, 1), '') AS season_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)
WHERE
a.id = %s
GROUP BY
oa.id
ORDER BY
`season_wins` DESC, `all_wins` DESC
""".format(all_select=deck.nwdl_all_select(),
season_select=deck.nwdl_season_select(),
nwdl_join=deck.nwdl_join())
return [
Container(m) for m in
db().execute(sql, [int(rotation.last_rotation().timestamp())] * 5 +
[archetype_id])
]
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 load_people(where='1 = 1', order_by='`season_num_decks` DESC, `all_num_decks` DESC, name'):
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 THEN 1 ELSE 0 END) AS `all_num_competitions`,
{season_select},
SUM(DISTINCT CASE WHEN d.created_date >= %s AND d.competition_id IS NOT NULL THEN 1 ELSE 0 END) AS `season_num_competitions`
FROM
person AS p
LEFT JOIN
deck AS d ON p.id = d.person_id
{nwdl_join}
WHERE
{where}
GROUP BY
p.id
ORDER BY
{order_by}
""".format(person_query=query.person_query(), all_select=deck.nwdl_all_select(), season_select=deck.nwdl_season_select(), nwdl_join=deck.nwdl_join(), where=where, order_by=order_by)
people = [Person(r) for r in db().execute(sql, [int(rotation.last_rotation().timestamp())])]
if len(people) > 0:
set_decks(people)
set_achievements(people)
set_head_to_head(people)
return people
def preaggregate_archetypes() -> None:
db().execute('DROP TABLE IF EXISTS _new_archetype_stats')
sql = """
CREATE TABLE IF NOT EXISTS _new_archetype_stats (
archetype_id INT NOT NULL,
season_id INT NOT NULL,
wins INT NOT NULL,
losses INT NOT NULL,
draws INT NOT NULL,
perfect_runs INT NOT NULL,
tournament_wins INT NOT NULL,
tournament_top8s INT NOT NULL,
wins_tournament INT NOT NULL,
losses_tournament INT NOT NULL,
draws_tournament INT NOT NULL,
PRIMARY KEY (season_id, archetype_id),
FOREIGN KEY (season_id) REFERENCES season (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (archetype_id) REFERENCES archetype (id) ON UPDATE CASCADE ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci AS
SELECT
a.id AS archetype_id,
season.id AS season_id,
SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS num_decks,
IFNULL(SUM(wins), 0) AS wins,
IFNULL(SUM(losses), 0) AS losses,
IFNULL(SUM(draws), 0) AS draws,
SUM(CASE WHEN wins >= 5 AND 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 dsum.finish = 1 THEN 1 ELSE 0 END) AS tournament_wins,
SUM(CASE WHEN dsum.finish <= 8 THEN 1 ELSE 0 END) AS tournament_top8s,
SUM(CASE WHEN (d.id IS NOT NULL) AND (ct.name = 'Gatherling') THEN 1 ELSE 0 END) AS num_decks_tournament,
IFNULL(SUM(CASE WHEN ct.name = 'Gatherling' THEN wins ELSE 0 END), 0) AS wins_tournament,
IFNULL(SUM(CASE WHEN ct.name = 'Gatherling' THEN losses ELSE 0 END), 0) AS losses_tournament,
IFNULL(SUM(CASE WHEN ct.name = 'Gatherling' THEN draws ELSE 0 END), 0) AS draws_tournament
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
{competition_join}
{season_join}
{nwdl_join}
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.
season.id
HAVING
season.id IS NOT NULL
""".format(competition_join=query.competition_join(),
season_join=query.season_join(),
nwdl_join=deck.nwdl_join())
db().execute(sql)
db().execute('DROP TABLE IF EXISTS _old_archetype_stats')
db().execute('CREATE TABLE IF NOT EXISTS _archetype_stats (_ INT)'
) # Prevent error in RENAME TABLE below if bootstrapping.
db().execute(
'RENAME TABLE _archetype_stats TO _old_archetype_stats, _new_archetype_stats TO _archetype_stats'
)
db().execute('DROP TABLE IF EXISTS _old_archetype_stats')
