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_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 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 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