async def get_matchstats_raw(league_tag: str, user_id: int) -> list:
params = (
user_id,
league_tag,
)
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
COUNT(*) AS wins,
MIN(winner_time) AS best_win,
AVG(winner_time) AS average_win
FROM {race_summary}
WHERE `winner_id` = %s AND `league_tag` = %s
LIMIT 1
""".format(race_summary=tn('race_summary')), params)
winner_data = cursor.fetchone()
if winner_data is None:
winner_data = [0, None, None]
cursor.execute(
"""
SELECT COUNT(*) AS losses
FROM {race_summary}
WHERE loser_id = %s AND `league_tag` = %s
LIMIT 1
""".format(race_summary=tn('race_summary')), params)
loser_data = cursor.fetchone()
if loser_data is None:
loser_data = [0]
return winner_data + loser_data
async def get_most_races_leaderboard(category_name: str, limit: int) -> list:
async with DBConnect(commit=False) as cursor:
params = (category_name, limit,)
cursor.execute(
"""
SELECT
user_name,
num_races
FROM
(
SELECT
users.discord_name as user_name,
SUM(
IF(
race_types.category=%s
AND NOT {0}.private,
1, 0
)
) as num_races
FROM {1}
INNER JOIN users ON users.user_id = {1}.user_id
INNER JOIN {0} ON {0}.race_id = {1}.race_id
INNER JOIN race_types ON race_types.type_id = {0}.type_id
GROUP BY users.discord_name
) tbl1
ORDER BY num_races DESC
LIMIT %s
""".format(tn('races'), tn('race_runs')),
params)
return cursor.fetchall()
async def _register_match(match: Match) -> None:
match_racetype_id = await racedb.get_race_type_id(race_info=match.race_info, register=True)
params = (
match_racetype_id,
match.racer_1.user_id,
match.racer_2.user_id,
match.suggested_time,
match.confirmed_by_r1,
match.confirmed_by_r2,
match.r1_wishes_to_unconfirm,
match.r2_wishes_to_unconfirm,
match.ranked,
match.is_best_of,
match.number_of_races,
match.cawmentator_id,
match.finish_time,
match.autogenned
)
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
INSERT INTO {matches}
(
race_type_id,
racer_1_id,
racer_2_id,
suggested_time,
r1_confirmed,
r2_confirmed,
r1_unconfirmed,
r2_unconfirmed,
ranked,
is_best_of,
number_of_races,
cawmentator_id,
finish_time,
autogenned
)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
""".format(matches=tn('matches')),
params
)
cursor.execute("SELECT LAST_INSERT_ID()")
match.set_match_id(int(cursor.fetchone()[0]))
params = (match.racer_1.user_id, match.racer_2.user_id,)
cursor.execute(
"""
INSERT IGNORE INTO {entrants} (user_id)
VALUES (%s), (%s)
""".format(entrants=tn('entrants')),
params
)
async def get_fastest_times_leaderboard(character_name: str, amplified: bool,
limit: int) -> list:
async with DBConnect(commit=False) as cursor:
params = {
'character': character_name,
'limit': limit,
}
cursor.execute(
"""
SELECT
users.`discord_name`,
mintimes.`min_time`,
{races}.`seed`,
{races}.`timestamp`
FROM
(
SELECT user_id, MIN(time) AS `min_time`
FROM {race_runs}
INNER JOIN {races} ON {races}.`race_id` = {race_runs}.`race_id`
INNER JOIN race_types ON race_types.`type_id` = {races}.`type_id`
WHERE
{race_runs}.`time` > 0
AND {race_runs}.`level` = -2
AND ({races}.`timestamp` > '2017-07-12' OR NOT race_types.`amplified`)
AND race_types.`character` = %(character)s
AND race_types.`descriptor` = 'All-zones'
AND race_types.`seeded`
AND {not_amplified}race_types.`amplified`
AND NOT {races}.`private`
GROUP BY user_id
) mintimes
INNER JOIN {race_runs}
ON ({race_runs}.`user_id` = mintimes.`user_id` AND {race_runs}.`time` = mintimes.`min_time`)
INNER JOIN {races} ON {races}.`race_id` = {race_runs}.`race_id`
INNER JOIN race_types ON race_types.`type_id` = {races}.`type_id`
INNER JOIN users ON users.`user_id` = mintimes.`user_id`
WHERE
{race_runs}.`level` = -2
AND ({races}.`timestamp` > '2017-07-12' OR NOT race_types.`amplified`)
AND race_types.`character` = %(character)s
AND race_types.`descriptor` = 'All-zones'
AND race_types.`seeded`
AND {not_amplified}race_types.`amplified`
AND NOT {races}.`private`
GROUP BY {race_runs}.`user_id`
ORDER BY mintimes.min_time ASC
LIMIT %(limit)s
""".format(races=tn('races'),
race_runs=tn('race_runs'),
not_amplified=('' if amplified else 'NOT ')), params)
return cursor.fetchall()
async def get_match_gsheet_duplication_number(match: Match) -> int:
"""
Parameters
----------
match: Match
A Match registered in the database.
Returns
-------
int
If this Match was created from a GSheet, the number of matches on the same worksheet and with
the same racers that appear in rows ahead of this match; otherwise, 0.
"""
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT COUNT(*)
FROM {matches}
WHERE
(racer_1_id = %(r1id)s OR racer_1_id = %(r2id)s)
AND (racer_2_id = %(r1id)s OR racer_2_id = %(r2id)s)
AND sheet_id = %(sheetid)s
AND sheet_row < %(sheetrow)s
""".format(matches=tn('matches')),
{
'r1id': match.racer_1.user_id,
'r2id': match.racer_2.user_id,
'sheetid': match.sheet_id,
'sheetrow': match.sheet_row,
}
)
return int(cursor.fetchone()[0])
async def get_raw_match_data(match_id: int) -> list:
params = (match_id,)
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
match_id,
race_type_id,
racer_1_id,
racer_2_id,
suggested_time,
r1_confirmed,
r2_confirmed,
r1_unconfirmed,
r2_unconfirmed,
ranked,
is_best_of,
number_of_races,
cawmentator_id,
channel_id,
sheet_id,
sheet_row,
finish_time
FROM {matches}
WHERE match_id=%s
LIMIT 1
""".format(matches=tn('matches')),
params
)
return cursor.fetchone()
async def record_match_race(
match: Match,
race_number: int = None,
race_id: int = None,
winner: int = None,
canceled: bool = False,
contested: bool = False
) -> None:
if race_number is None:
race_number = await _get_new_race_number(match)
async with DBConnect(commit=True) as cursor:
params = (
match.match_id,
race_number,
race_id,
winner,
canceled,
contested
)
cursor.execute(
"""
INSERT INTO {match_races}
(match_id, race_number, race_id, winner, canceled, contested)
VALUES (%s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
race_id=VALUES(race_id),
winner=VALUES(winner),
canceled=VALUES(canceled),
contested=VALUES(contested)
""".format(match_races=tn('match_races')),
params
)
async def get_match_race_data(match_id: int) -> MatchRaceData:
params = (match_id,)
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT canceled, winner
FROM {match_races}
WHERE match_id=%s
""".format(match_races=tn('match_races')),
params
)
finished = 0
canceled = 0
r1_wins = 0
r2_wins = 0
for row in cursor:
if bool(row[0]):
canceled += 1
else:
finished += 1
if int(row[1]) == 1:
r1_wins += 1
elif int(row[1]) == 2:
r2_wins += 1
return MatchRaceData(finished=finished, canceled=canceled, r1_wins=r1_wins, r2_wins=r2_wins)
async def get_all_racedata(user_id: int, cat_name: str) -> list:
async with DBConnect(commit=False) as cursor:
params = (user_id, cat_name)
cursor.execute(
"""
SELECT {1}.`time`, {1}.`level`
FROM {1}
INNER JOIN {0} ON {0}.`race_id` = {1}.`race_id`
INNER JOIN `race_types` ON {0}.`type_id` = `race_types`.`type_id`
WHERE {1}.`user_id` = %s
AND `race_types`.`category` = %s
AND NOT {0}.`private`
""".format(tn('races'), tn('race_runs')),
params
)
return cursor.fetchall()
async def submit(
necro_user: NecroUser,
category_race_info: RaceInfo,
category_score: int,
vod_url: str,
submission_time: datetime.datetime = None
) -> None:
category_type_id = await racedb.get_race_type_id(race_info=category_race_info, register=True)
params = (
necro_user.user_id,
category_type_id,
category_score,
vod_url,
submission_time
)
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
INSERT INTO {speedruns}
(user_id, type_id, score, vod, submission_time)
VALUES (%s, %s, %s, %s, %s)
""".format(speedruns=tn('speedruns')),
params
)
async def get_public_race_numbers(user_id: int) -> list:
async with DBConnect(commit=False) as cursor:
params = (user_id,)
cursor.execute(
"""
SELECT `race_types`.`category`, COUNT(*) as num
FROM {1}
INNER JOIN {0} ON {0}.`race_id` = {1}.`race_id`
INNER JOIN race_types ON {0}.`type_id` = `race_types`.`type_id`
WHERE {1}.`user_id` = %s
AND NOT {0}.`private`
GROUP BY `race_types`.`category`
ORDER BY num DESC
""".format(tn('races'), tn('race_runs')),
params)
return cursor.fetchall()
async def get_fastest_wins_raw(league_tag: str, limit: int = None) -> list:
params = (
league_tag,
limit,
)
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
{race_runs}.`time` AS `time`,
users_winner.twitch_name AS winner_name,
users_loser.twitch_name AS loser_name,
{matches}.suggested_time AS match_time
FROM
{match_races}
INNER JOIN {matches}
ON {matches}.match_id = {match_races}.match_id
INNER JOIN {races}
ON {races}.race_id = {match_races}.race_id
INNER JOIN users users_winner
ON IF(
{match_races}.winner = 1,
users_winner.`user_id` = {matches}.racer_1_id,
users_winner.`user_id` = {matches}.racer_2_id
)
INNER JOIN users users_loser
ON IF(
{match_races}.winner = 1,
users_loser.user_id = {matches}.racer_2_id,
users_loser.user_id = {matches}.racer_1_id
)
INNER JOIN {race_runs}
ON (
{race_runs}.race_id = {races}.race_id
AND {race_runs}.user_id = users_winner.user_id
)
WHERE
{match_races}.winner != 0
AND {matches}.`league_tag` = %s
AND NOT {match_races}.canceled
ORDER BY `time` ASC
LIMIT %s
""".format(race_runs=tn('race_runs'),
matches=tn('matches'),
match_races=tn('match_races'),
races=tn('races')), params)
return cursor.fetchall()
async def delete_match(match_id: int):
params = (match_id,)
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
DELETE FROM {match_races}
WHERE `match_id`=%s
""".format(match_races=tn('match_races')),
params
)
cursor.execute(
"""
DELETE FROM {matches}
WHERE `match_id`=%s
""".format(matches=tn('matches')),
params
)
async def get_most_races_leaderboard(character_name: str, limit: int) -> list:
async with DBConnect(commit=False) as cursor:
params = (
character_name,
character_name,
limit,
)
cursor.execute(
"""
SELECT
user_name,
num_predlc + num_postdlc as total,
num_predlc,
num_postdlc
FROM
(
SELECT
users.discord_name as user_name,
SUM(
IF(
race_types.character=%s
AND race_types.descriptor='All-zones'
AND NOT race_types.amplified
AND NOT {0}.private,
1, 0
)
) as num_predlc,
SUM(
IF(
race_types.character=%s
AND race_types.descriptor='All-zones'
AND race_types.amplified
AND NOT {0}.private,
1, 0
)
) as num_postdlc
FROM {1}
INNER JOIN users ON users.user_id = {1}.user_id
INNER JOIN {0} ON {0}.race_id = {1}.race_id
INNER JOIN race_types ON race_types.type_id = {0}.type_id
GROUP BY users.discord_name
) tbl1
ORDER BY total DESC
LIMIT %s
""".format(tn('races'), tn('race_runs')), params)
return cursor.fetchall()
async def get_league(league_tag: str) -> League:
"""
Parameters
----------
league_tag: str
The unique identifier for the league
Returns
-------
League
A League object for the event.
"""
params = (league_tag, )
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
{leagues}.`league_tag`,
{leagues}.`league_name`,
{leagues}.`number_of_races`,
{leagues}.`is_best_of`,
{leagues}.`worksheet_id`,
`race_types`.`character`,
`race_types`.`descriptor`,
`race_types`.`seeded`,
`race_types`.`amplified`,
`race_types`.`seed_fixed`
FROM {leagues}
LEFT JOIN `race_types` ON `leagues`.`race_type` = `race_types`.`type_id`
WHERE {leagues}.`league_tag` = %s
LIMIT 1
""".format(leagues=tn('leagues')), params)
for row in cursor:
race_info = RaceInfo()
if row[5] is not None:
race_info.set_char(row[5])
if row[6] is not None:
race_info.descriptor = row[6]
if row[7] is not None:
race_info.seeded = bool(row[7])
if row[8] is not None:
race_info.amplified = bool(row[8])
if row[9] is not None:
race_info.seed_fixed = bool(row[9])
match_info = MatchInfo(
max_races=int(row[2]) if row[2] is not None else None,
is_best_of=bool(row[3]) if row[3] is not None else None,
ranked=None,
race_info=race_info)
return League(commit_fn=write_league,
league_tag=row[0],
league_name=row[1],
match_info=match_info,
worksheet_id=row[4])
raise necrobot.exception.LeagueDoesNotExist()
async def get_allzones_race_numbers(user_id: int, amplified: bool) -> list:
async with DBConnect(commit=False) as cursor:
params = (user_id, )
cursor.execute(
"""
SELECT `race_types`.`character`, COUNT(*) as num
FROM {1}
INNER JOIN {0} ON {0}.`race_id` = {1}.`race_id`
INNER JOIN race_types ON {0}.`type_id` = `race_types`.`type_id`
WHERE {1}.`user_id` = %s
AND `race_types`.`descriptor` = 'All-zones'
AND {2}`race_types`.`amplified`
AND `race_types`.`seeded` AND NOT {0}.`private`
GROUP BY `race_types`.`character`
ORDER BY num DESC
""".format(tn('races'), tn('race_runs'),
'NOT ' if not amplified else ''), params)
return cursor.fetchall()
async def get_all_racedata(user_id: int, char_name: str,
amplified: bool) -> list:
async with DBConnect(commit=False) as cursor:
params = (user_id, char_name)
cursor.execute(
"""
SELECT {1}.`time`, {1}.`level`
FROM {1}
INNER JOIN {0} ON {0}.`race_id` = {1}.`race_id`
INNER JOIN `race_types` ON {0}.`type_id` = `race_types`.`type_id`
WHERE {1}.`user_id` = %s
AND `race_types`.`character` = %s
AND `race_types`.`descriptor` = 'All-zones'
AND {2}`race_types`.`amplified`
AND race_types.seeded AND NOT {0}.`private`
""".format(tn('races'), tn('race_runs'),
'NOT ' if not amplified else ''), params)
return cursor.fetchall()
async def get_all_matches_raw_data(
must_be_channeled: bool = False,
must_be_scheduled: bool = False,
order_by_time: bool = False,
racer_id: int = None,
limit: int = None
) -> list:
params = tuple()
where_query = 'TRUE'
if must_be_channeled:
where_query += " AND `channel_id` IS NOT NULL"
if must_be_scheduled:
where_query += " AND (`suggested_time` IS NOT NULL AND `r1_confirmed` AND `r2_confirmed`)"
if racer_id is not None:
where_query += " AND (`racer_1_id` = %s OR `racer_2_id` = %s)"
params += (racer_id, racer_id,)
order_query = ''
if order_by_time:
order_query = "ORDER BY `suggested_time` ASC"
limit_query = '' if limit is None else 'LIMIT {}'.format(limit)
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
match_id,
race_type_id,
racer_1_id,
racer_2_id,
suggested_time,
r1_confirmed,
r2_confirmed,
r1_unconfirmed,
r2_unconfirmed,
ranked,
is_best_of,
number_of_races,
cawmentator_id,
channel_id,
sheet_id,
sheet_row,
finish_time,
autogenned
FROM {matches}
WHERE {where_query} {order_query}
{limit_query}
""".format(
matches=tn('matches'),
where_query=where_query,
order_query=order_query,
limit_query=limit_query
), params)
return cursor.fetchall()
async def scrub_unchanneled_unraced_matches() -> None:
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
DELETE {matches}
FROM {matches}
LEFT JOIN (
SELECT
match_id,
COUNT(*) AS number_of_races
FROM {match_races}
GROUP BY match_id
) match_counts ON match_counts.match_id = {matches}.match_id
WHERE match_counts.number_of_races IS NULL AND {matches}.channel_id IS NULL
""".format(
matches=tn('matches'),
match_races=tn('match_races')
)
)
async def get_match_id(
racer_1_id: int,
racer_2_id: int,
scheduled_time: datetime.datetime = None
) -> int or None:
"""Attempt to find a match between the two racers
If multiple matches are found, prioritize as follows:
1. Prefer matches closer to scheduled_time, if scheduled_time is not None
2. Prefer channeled matches
3. Prefer the most recent scheduled match
4. Randomly
Parameters
----------
racer_1_id: int
The user ID of the first racer
racer_2_id: int
The user ID of the second racer
scheduled_time: datetime.datetime or None
The approximate time to search around, or None to skip this priority
Returns
-------
Optional[int]
The match ID, if one is found.
"""
param_dict = {
'racer1': racer_1_id,
'racer2': racer_2_id,
'time': scheduled_time
}
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
match_id,
suggested_time,
channel_id,
ABS(`suggested_time` - '2017-23-04 12:00:00') AS abs_del
FROM {matches}
WHERE
(racer_1_id=%(racer1)s AND racer_2_id=%(racer2)s) OR (racer_1_id=%(racer2)s AND racer_2_id=%(racer1)s)
ORDER BY
IF(%(time)s IS NULL, 0, -ABS(`suggested_time` - %(time)s)) DESC,
`channel_id` IS NULL ASC,
`suggested_time` DESC
LIMIT 1
""".format(matches=tn('matches')),
param_dict
)
row = cursor.fetchone()
return int(row[0]) if row is not None else None
async def register_match_channel(match_id: int, channel_id: int or None) -> None:
params = (channel_id, match_id,)
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
UPDATE {matches}
SET channel_id=%s
WHERE match_id=%s
""".format(matches=tn('matches')),
params
)
async def set_verified(run_id: int, verified: bool):
async with DBConnect(commit=True) as cursor:
params = (verified, run_id,)
cursor.execute(
"""
UPDATE {speedruns}
SET verified = %s
WHERE submission_id = %s
""".format(speedruns=tn('speedruns')),
params
)
async def cancel_match(match: Match) -> bool:
params = (match.match_id,)
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
DELETE
FROM {match_races}
WHERE `match_id` = %s
""".format(match_races=tn('match_races')),
params
)
cursor.execute(
"""
DELETE
FROM {matches}
WHERE `match_id` = %s
""".format(matches=tn('matches')),
params
)
return True
async def write_match(match: Match):
if not match.is_registered:
await _register_match(match)
match_racetype_id = await racedb.get_race_type_id(race_info=match.race_info, register=True)
params = (
match_racetype_id,
match.racer_1.user_id,
match.racer_2.user_id,
match.suggested_time,
match.confirmed_by_r1,
match.confirmed_by_r2,
match.r1_wishes_to_unconfirm,
match.r2_wishes_to_unconfirm,
match.ranked,
match.is_best_of,
match.number_of_races,
match.cawmentator_id,
match.channel_id,
match.sheet_id,
match.sheet_row,
match.finish_time,
match.autogenned,
match.match_id,
)
async with DBConnect(commit=True) as cursor:
cursor.execute(
"""
UPDATE {matches}
SET
race_type_id=%s,
racer_1_id=%s,
racer_2_id=%s,
suggested_time=%s,
r1_confirmed=%s,
r2_confirmed=%s,
r1_unconfirmed=%s,
r2_unconfirmed=%s,
ranked=%s,
is_best_of=%s,
number_of_races=%s,
cawmentator_id=%s,
channel_id=%s,
sheet_id=%s,
sheet_row=%s,
finish_time=%s,
autogenned=%s
WHERE match_id=%s
""".format(matches=tn('matches')),
params
)
async def record_race(race: Race) -> None:
type_id = await get_race_type_id(race.race_info, register=True)
async with DBConnect(commit=True) as cursor:
# Record the race
race_params = (
race.start_datetime.strftime('%Y-%m-%d %H:%M:%S'),
type_id,
race.race_info.seed if race.race_info.seeded else 0,
race.race_info.condor_race,
race.race_info.private_race,
)
cursor.execute(
"""
INSERT INTO {0}
(timestamp, type_id, seed, condor, private)
VALUES (%s,%s,%s,%s,%s)
""".format(tn('races')),
race_params
)
# Store the new race ID in the Race object
cursor.execute("SELECT LAST_INSERT_ID()")
race.race_id = int(cursor.fetchone()[0])
# Record each racer in race_runs
rank = 1
for racer in race.racers:
racer_params = (race.race_id, racer.user_id, racer.time, rank, racer.igt, racer.comment, racer.level)
cursor.execute(
"""
INSERT INTO {0}
(race_id, user_id, time, rank, igt, comment, level)
VALUES (%s,%s,%s,%s,%s,%s,%s)
""".format(tn('race_runs')),
racer_params
)
if racer.is_finished:
rank += 1
async def get_largest_race_number(user_id: int) -> int:
async with DBConnect(commit=False) as cursor:
params = (user_id, )
cursor.execute(
"""
SELECT race_id
FROM {0}
WHERE user_id = %s
ORDER BY race_id DESC
LIMIT 1
""".format(tn('race_runs')), params)
row = cursor.fetchone()
return int(row[0]) if row is not None else 0
async def get_matches_between(user_1_id, user_2_id):
params = (user_1_id, user_2_id, user_2_id, user_1_id,)
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT
match_id
FROM {matches}
WHERE (racer_1_id = %s AND racer_2_id = %s) OR (racer_2_id = %s AND racer_1_id = %s)
""".format(matches=tn('matches')),
params
)
return cursor.fetchall()
async def get_match_channel_id(match_id: int) -> int:
params = (match_id,)
async with DBConnect(commit=False) as cursor:
cursor.execute(
"""
SELECT channel_id
FROM {matches}
WHERE match_id=%s
LIMIT 1
""".format(matches=tn('matches')),
params
)
row = cursor.fetchone()
return int(row[0]) if row[0] is not None else None
async def get_fastest_times_leaderboard(category_name: str, limit: int) -> list:
async with DBConnect(commit=False) as cursor:
params = {'category': category_name, 'limit': limit,}
cursor.execute(
"""
SELECT
users.`discord_name`,
mintimes.`min_time`,
{races}.`seed`,
{races}.`timestamp`
FROM
(
SELECT user_id, MIN(time) AS `min_time`
FROM {race_runs}
INNER JOIN {races} ON {races}.`race_id` = {race_runs}.`race_id`
INNER JOIN race_types ON race_types.`type_id` = {races}.`type_id`
WHERE
{race_runs}.time > 0
AND {race_runs}.level = -2
AND race_types.category=%(category)s
AND NOT {races}.private
GROUP BY user_id
) mintimes
INNER JOIN {race_runs} ON ({race_runs}.`user_id` = mintimes.`user_id` AND {race_runs}.`time` = mintimes.`min_time`)
INNER JOIN {races} ON {races}.`race_id` = {race_runs}.`race_id`
INNER JOIN race_types ON race_types.`type_id` = {races}.`type_id`
INNER JOIN users ON users.`user_id` = mintimes.`user_id`
WHERE
{race_runs}.`level` = -2
AND race_types.category=%(category)s
AND NOT {races}.`private`
GROUP BY {race_runs}.`user_id`
ORDER BY mintimes.min_time ASC
LIMIT %(limit)s
""".format(races=tn('races'), race_runs=tn('race_runs')),
params)
return cursor.fetchall()
async def get_fastest_times_leaderboard(character_name: str, amplified: bool,
limit: int) -> list:
async with DBConnect(commit=False) as cursor:
params = (
character_name,
limit,
)
cursor.execute(
"""
SELECT users.discord_name, {race_runs}.time, {races}.seed, {races}.timestamp
FROM {race_runs}
INNER JOIN
(
SELECT user_id, MIN(time) AS min_time
FROM {race_runs}
INNER JOIN {races} ON {races}.race_id = {race_runs}.race_id
INNER JOIN race_types ON race_types.type_id = {races}.type_id
WHERE
{race_runs}.time > 0
AND {race_runs}.level = -2
AND ({races}.timestamp > '2017-07-12' OR NOT race_types.amplified)
AND race_types.character=%s
AND race_types.descriptor='All-zones'
AND race_types.seeded
AND {not_amplified}race_types.amplified
AND NOT {races}.private
GROUP BY user_id
) rd1 On rd1.user_id = {race_runs}.user_id
INNER JOIN users ON users.user_id = {race_runs}.user_id
INNER JOIN {races} ON {races}.race_id = {race_runs}.race_id
WHERE {race_runs}.time = rd1.min_time
ORDER BY {race_runs}.time ASC
LIMIT %s
""".format(races=tn('races'),
race_runs=tn('race_runs'),
not_amplified=('' if amplified else 'NOT ')), params)
return cursor.fetchall()
