def load_people(where: str = 'TRUE',
order_by_name: bool = False,
season_id: Optional[int] = None) -> Sequence[Person]:
# First we retrieve the people because we want to return a Person for each person that exists even if they have no decks.
# This is two separate queries for performance reasons. See #5564.
sql = """
SELECT
p.id,
{person_query} AS name,
p.mtgo_username,
p.tappedout_username,
p.mtggoldfish_username,
p.discord_id,
p.elo,
p.locale
FROM
person AS p
WHERE
{where}
""".format(person_query=query.person_query(), where=where)
people = [Person(r) for r in db().select(sql)]
stats = load_people_stats(where, season_id)
for p in people:
p.update(stats.get(p.id, {}))
p.season_id = season_id
if order_by_name:
people.sort(key=lambda p: p.get('name') or 'ZZZZZZZZZZ')
else:
people.sort(key=lambda p:
(-p.get('num_decks', 0), 1)) # (, p.get('name')))
return people
def display(self, p: Person) -> str:
n = p.get('achievements', {}).get(self.key, 0)
if n > 0:
if decksite.get_season_id() == 0:
return self.alltime_text(n)
return self.season_text
return ''
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 load_person_statless(where: str = 'TRUE',
season_id: Optional[int] = None) -> Person:
person_query = query.person_query()
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
FROM
person AS p
WHERE
{where}
"""
people = [Person(r) for r in db().select(sql)]
for p in people:
p.season_id = season_id
return guarantee.exactly_one(people)
def display(self, p: Person) -> str:
n = p.get('achievements', {}).get(self.key, 0)
if n > 0:
return self.localised_display(n)
return ''