9
3
Iran, Islamische Republik
Qom
570
107
2021-05-14 03:10:26
10
3
Iran, Islamische Republik
Qom
0
0
2021-05-14 03:32:40
Ich brauche die Rangzahl für einen Benutzer nach:
Gesamtpunktzahl: Global
Gesamtpunktzahl: Land
Gesamtpunktzahl: Region
Highscore: Global
Highscore: Land
Highscore: Region
Und auch Bestenlisten dieser 6 Segmente für die Top 10 Benutzer. 12 Datensätze, die ich mit 12 separaten Abfragen abrufe. Ich möchte diese mit MySQL 5.7 kombinieren und optimieren. Aktuelle Implementierung:
public function leaderboard(Request $request)
{
$user = $request->input('_user');
if (!$user) {
return apiSend(null, 401);
}
$data = [
'total' => [
'player' => [
'score' => 0,
'global_rank' => 0,
'country_rank' => 0,
'region_rank' => 0,
],
'global' => [], 'country' => [], 'region' => [],
],
'high' => [
'player' => [
'score' => 0,
'global_rank' => 0,
'country_rank' => 0,
'region_rank' => 0,
],
'global' => [], 'country' => [], 'region' => [],
],
];
$countryWhereClause = $user->country ? "WHERE country = '{$user->country}'" : 'WHERE country IS NULL';
$regionWhereClause = $user->region ? "AND region = '{$user->region}'" : 'AND region IS NULL';
$totalGlobalRank = DB::select("SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = {$user->id}");
$totalCountryRank = DB::select("SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances {$countryWhereClause} GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = {$user->id}");
$totalRegionRank = DB::select("SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances {$countryWhereClause} {$regionWhereClause} GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = {$user->id}");
$highGlobalRank = DB::select("SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = {$user->id}");
$highCountryRank = DB::select("SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances {$countryWhereClause} GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = {$user->id}");
$highRegionRank = DB::select("SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances {$countryWhereClause} {$regionWhereClause} GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = {$user->id}");
if (count($totalGlobalRank)) {
$data['total']['player']['score'] = $totalGlobalRank[0]->total_score;
$data['total']['player']['global_rank'] = $totalGlobalRank[0]->rank;
}
if (count($totalCountryRank)) {
$data['total']['player']['country_rank'] = $totalCountryRank[0]->rank;
}
if (count($totalRegionRank)) {
$data['total']['player']['region_rank'] = $totalRegionRank[0]->rank;
}
if (count($highGlobalRank)) {
$data['high']['player']['score'] = $highGlobalRank[0]->max_score;
$data['high']['player']['global_rank'] = $highGlobalRank[0]->rank;
}
if (count($highCountryRank)) {
$data['high']['player']['country_rank'] = $highCountryRank[0]->rank;
}
if (count($highRegionRank)) {
$data['high']['player']['region_rank'] = $highRegionRank[0]->rank;
}
$countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL';
$regionWhereClause = $user->region ? "AND g.region = '{$user->region}'" : 'AND g.region IS NULL';
$totalGlobalLeaderboards = $this->totalGlobalLeaderboards($user);
$totalCountryLeaderboards = $this->totalCountryLeaderboards($user);
$totalRegionLeaderboards = $this->totalRegionLeaderboards($user);
$highGlobalLeaderboards = $this->highGlobalLeaderboards($user);
$highCountryLeaderboards = $this->highCountryLeaderboards($user);
$highRegionLeaderboards = $this->highRegionLeaderboards($user);
foreach ($totalGlobalLeaderboards as $r) {
$data['total']['global'][] = ['name' => $r->name, 'score' => $r->total_score, 'rank' => $r->rank, 'is_user' => $user->id == $r->user_id];
}
foreach ($totalCountryLeaderboards as $r) {
$data['total']['country'][] = ['name' => $r->name, 'score' => (int) $r->total_score, 'rank' => $r->rank, 'is_user' => $user->id == $r->user_id];
}
foreach ($totalRegionLeaderboards as $r) {
$data['total']['region'][] = ['name' => $r->name, 'score' => (int) $r->total_score, 'rank' => $r->rank, 'is_user' => $user->id == $r->user_id];
}
foreach ($highGlobalLeaderboards as $r) {
$data['high']['global'][] = ['name' => $r->name, 'score' => (int) $r->max_score, 'rank' => $r->rank, 'is_user' => $user->id == $r->user_id];
}
foreach ($highCountryLeaderboards as $r) {
$data['high']['country'][] = ['name' => $r->name, 'score' => (int) $r->max_score, 'rank' => $r->rank, 'is_user' => $user->id == $r->user_id];
}
foreach ($highRegionLeaderboards as $r) {
$data['high']['region'][] = ['name' => $r->name, 'score' => (int) $r->max_score, 'rank' => $r->rank, 'is_user' => $user->id == $r->user_id];
}
return apiSend($data);
}
// Returns leaderboards with ranks near user rank, if user is not in the top 10.
public function userLeaderboards(Request $request)
{
$user = $request->input('_user');
if (!$user) {
return apiSend(null, 401);
}
$rank = (int) $request->query('rank');
$type = $request->query('type');
$scope = $request->query('scope');
if ($rank totalGlobalLeaderboards($user, $rank);
break;
case $type == 'total' && $scope == 'country':
$rows = $this->totalCountryLeaderboards($user, $rank);
break;
case $type == 'total' && $scope == 'region':
$rows = $this->totalRegionLeaderboards($user, $rank);
break;
case $type == 'high' && $scope == 'global':
$rows = $this->highGlobalLeaderboards($user, $rank);
break;
case $type == 'high' && $scope == 'country':
$rows = $this->highCountryLeaderboards($user, $rank);
break;
case $type == 'high' && $scope == 'region':
$rows = $this->highRegionLeaderboards($user, $rank);
break;
default:
return apiSend(null, 400);
}
foreach ($rows as $r) {
$data[] = ['name' => $r->name, 'score' => (int) ($type == 'total' ? $r->total_score : $r->max_score), 'rank' => (int) $r->rank, 'is_user' => $user->id == $r->user_id];
}
return apiSend($data);
}
private function totalGlobalLeaderboards($user, $rank = 0)
{
if (!$user) {
return [];
}
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : '';
$rowNumInit = $rank > 10 ? $rank - 6 : 0;
return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, SUM(g.score) AS total_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id GROUP BY user_id ORDER BY total_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r");
}
private function totalCountryLeaderboards($user, $rank = 0)
{
if (!$user) {
return [];
}
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : '';
$rowNumInit = $rank > 10 ? $rank - 6 : 0;
$countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL';
return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, SUM(g.score) AS total_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id {$countryWhereClause} GROUP BY user_id ORDER BY total_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r");
}
private function totalRegionLeaderboards($user, $rank = 0)
{
if (!$user) {
return [];
}
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : '';
$rowNumInit = $rank > 10 ? $rank - 6 : 0;
$countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL';
$regionWhereClause = $user->region ? "AND g.region = '{$user->region}'" : 'AND g.region IS NULL';
return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, SUM(g.score) AS total_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id {$countryWhereClause} {$regionWhereClause} GROUP BY user_id ORDER BY total_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r");
}
private function highGlobalLeaderboards($user, $rank = 0)
{
if (!$user) {
return [];
}
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : '';
$rowNumInit = $rank > 10 ? $rank - 6 : 0;
return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, MAX(g.score) AS max_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id GROUP BY user_id ORDER BY max_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r");
}
private function highCountryLeaderboards($user, $rank = 0)
{
if (!$user) {
return [];
}
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : '';
$rowNumInit = $rank > 10 ? $rank - 6 : 0;
$countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL';
return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, MAX(g.score) AS max_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id {$countryWhereClause} GROUP BY user_id ORDER BY max_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r");
}
private function highRegionLeaderboards($user, $rank = 0)
{
if (!$user) {
return [];
}
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : '';
$rowNumInit = $rank > 10 ? $rank - 6 : 0;
$countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL';
$regionWhereClause = $user->region ? "AND g.region = '{$user->region}'" : 'AND g.region IS NULL';
return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, MAX(g.score) AS max_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id {$countryWhereClause} {$regionWhereClause} GROUP BY user_id ORDER BY max_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r");
}
Ich weiß nicht, ob es möglich ist, Leaderboard-Abfragen zu kombinieren, da jede eine eigene Ergebnismenge und keine einzelne Zeile zurückgibt. Es gibt 6 Bestenlisten: (total_score, max_score) x (global, Land, Region). Jeder enthält maximal 10 Datensätze:
// SELECT ... WHERE user_id = 1;
{
"total_score_global": 3540,
"total_score_global_rank": 13,
"total_score_country": 2830,
"total_score_country_rank": 6,
"total_score_region": 2600,
"total_score_region_rank": 2,
"max_score_global": 1084,
"max_score_global_rank": 19,
"max_score_country": 0, // No data for this user with given country.
"max_score_country_rank": 0, // So his rank will be 0.
"max_score_region": 950,
"max_score_region_rank": 1,
}
SELECT
r1.total_score AS total_score_global, r1.rank AS total_score_global_rank,
r2.total_score AS total_score_country, r2.rank AS total_score_country_rank,
r3.total_score AS total_score_region, r3.rank AS total_score_region_rank,
r4.max_score AS max_score_global, r4.rank AS max_score_global_rank,
r5.max_score AS max_score_country, r5.rank AS max_score_country_rank,
r6.max_score AS max_score_region, r6.rank AS max_score_region_rank,
FROM
(SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = 1) r1,
(SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances WHERE country = 'cname' GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = 1) r2,
(SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances WHERE country = 'cname' AND region = 'rname' GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = 1) r3,
(SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = 1) r4,
(SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances WHERE country = 'cname' GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = 1) r5,
(SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances WHERE country = 'cname' AND region = 'rname' GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = 1) r6
9 3 Iran, Islamische Republik Qom 570 107 2021-05-14 03:10:26
10 3 Iran, Islamische Republik Qom 0 0 2021-05-14 03:32:40
Ich brauche die Rangzahl für einen Benutzer nach: [list] [*]Gesamtpunktzahl: Global [*]Gesamtpunktzahl: Land[*]Gesamtpunktzahl: Region [*]Highscore: Global [*]Highscore: Land [*]Highscore: Region [/list] Und auch Bestenlisten dieser 6 Segmente für die Top 10 Benutzer. 12 Datensätze, die ich mit 12 separaten Abfragen abrufe. Ich möchte diese mit MySQL 5.7 kombinieren und optimieren. Aktuelle Implementierung: [code]public function leaderboard(Request $request) { $user = $request->input('_user'); if (!$user) { return apiSend(null, 401); }
$countryWhereClause = $user->country ? "WHERE country = '{$user->country}'" : 'WHERE country IS NULL'; $regionWhereClause = $user->region ? "AND region = '{$user->region}'" : 'AND region IS NULL';
$totalGlobalRank = DB::select("SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = {$user->id}"); $totalCountryRank = DB::select("SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances {$countryWhereClause} GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = {$user->id}"); $totalRegionRank = DB::select("SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances {$countryWhereClause} {$regionWhereClause} GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = {$user->id}"); $highGlobalRank = DB::select("SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = {$user->id}"); $highCountryRank = DB::select("SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances {$countryWhereClause} GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = {$user->id}"); $highRegionRank = DB::select("SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances {$countryWhereClause} {$regionWhereClause} GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = {$user->id}"); if (count($totalGlobalRank)) { $data['total']['player']['score'] = $totalGlobalRank[0]->total_score; $data['total']['player']['global_rank'] = $totalGlobalRank[0]->rank; } if (count($totalCountryRank)) { $data['total']['player']['country_rank'] = $totalCountryRank[0]->rank; } if (count($totalRegionRank)) { $data['total']['player']['region_rank'] = $totalRegionRank[0]->rank; }
if (count($highGlobalRank)) { $data['high']['player']['score'] = $highGlobalRank[0]->max_score; $data['high']['player']['global_rank'] = $highGlobalRank[0]->rank; } if (count($highCountryRank)) { $data['high']['player']['country_rank'] = $highCountryRank[0]->rank; } if (count($highRegionRank)) { $data['high']['player']['region_rank'] = $highRegionRank[0]->rank; }
// Returns leaderboards with ranks near user rank, if user is not in the top 10. public function userLeaderboards(Request $request) { $user = $request->input('_user'); if (!$user) { return apiSend(null, 401); }
private function totalGlobalLeaderboards($user, $rank = 0) { if (!$user) { return []; }
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : ''; $rowNumInit = $rank > 10 ? $rank - 6 : 0; return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, SUM(g.score) AS total_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id GROUP BY user_id ORDER BY total_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r"); }
private function totalCountryLeaderboards($user, $rank = 0) { if (!$user) { return []; }
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : ''; $rowNumInit = $rank > 10 ? $rank - 6 : 0; $countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL'; return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, SUM(g.score) AS total_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id {$countryWhereClause} GROUP BY user_id ORDER BY total_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r"); }
private function totalRegionLeaderboards($user, $rank = 0) { if (!$user) { return []; }
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : ''; $rowNumInit = $rank > 10 ? $rank - 6 : 0; $countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL'; $regionWhereClause = $user->region ? "AND g.region = '{$user->region}'" : 'AND g.region IS NULL'; return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, SUM(g.score) AS total_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id {$countryWhereClause} {$regionWhereClause} GROUP BY user_id ORDER BY total_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r"); }
private function highGlobalLeaderboards($user, $rank = 0) { if (!$user) { return []; }
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : ''; $rowNumInit = $rank > 10 ? $rank - 6 : 0; return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, MAX(g.score) AS max_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id GROUP BY user_id ORDER BY max_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r"); }
private function highCountryLeaderboards($user, $rank = 0) { if (!$user) { return []; }
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : ''; $rowNumInit = $rank > 10 ? $rank - 6 : 0; $countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL'; return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, MAX(g.score) AS max_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id {$countryWhereClause} GROUP BY user_id ORDER BY max_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r"); }
private function highRegionLeaderboards($user, $rank = 0) { if (!$user) { return []; }
$offset = $rank > 10 ? sprintf('OFFSET %d', $rank - 6) : ''; $rowNumInit = $rank > 10 ? $rank - 6 : 0; $countryWhereClause = $user->country ? "WHERE g.country = '{$user->country}'" : 'WHERE g.country IS NULL'; $regionWhereClause = $user->region ? "AND g.region = '{$user->region}'" : 'AND g.region IS NULL'; return DB::select("SELECT t.*, @rownum := @rownum + 1 AS rank FROM (SELECT u.name, g.user_id, MAX(g.score) AS max_score FROM game_instances g INNER JOIN users u ON u.id = g.user_id {$countryWhereClause} {$regionWhereClause} GROUP BY user_id ORDER BY max_score DESC LIMIT 10 {$offset}) t, (SELECT @rownum := {$rowNumInit}) r"); } [/code] Ich weiß nicht, ob es möglich ist, Leaderboard-Abfragen zu kombinieren, da jede eine eigene Ergebnismenge und keine einzelne Zeile zurückgibt. Es gibt 6 Bestenlisten: (total_score, max_score) x (global, Land, Region). Jeder enthält maximal 10 Datensätze: [code][ { "name": "Mina", // Joined from users table. "user_id": 1, "toal_score": 7400, // Or max_score for Max Score Leaderboards. "rank": 1, }, {...} ] [/code] Ich denke, es ist möglich, alle Rangabfragen zu kombinieren, da sie nur eine Zeile zurückgeben: [code]// SELECT ... WHERE user_id = 1; { "total_score_global": 3540, "total_score_global_rank": 13, "total_score_country": 2830, "total_score_country_rank": 6, "total_score_region": 2600, "total_score_region_rank": 2,
"max_score_global": 1084, "max_score_global_rank": 19, "max_score_country": 0, // No data for this user with given country. "max_score_country_rank": 0, // So his rank will be 0. "max_score_region": 950, "max_score_region_rank": 1, } [/code] Einer meiner Versuche: [code]SELECT r1.total_score AS total_score_global, r1.rank AS total_score_global_rank, r2.total_score AS total_score_country, r2.rank AS total_score_country_rank, r3.total_score AS total_score_region, r3.rank AS total_score_region_rank, r4.max_score AS max_score_global, r4.rank AS max_score_global_rank, r5.max_score AS max_score_country, r5.rank AS max_score_country_rank, r6.max_score AS max_score_region, r6.rank AS max_score_region_rank, FROM (SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = 1) r1, (SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances WHERE country = 'cname' GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = 1) r2, (SELECT t1.user_id, t1.rank, t1.total_score FROM (SELECT p.user_id, p.total_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, SUM(score) AS total_score FROM game_instances WHERE country = 'cname' AND region = 'rname' GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.total_score DESC) t1 WHERE t1.user_id = 1) r3, (SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = 1) r4, (SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances WHERE country = 'cname' GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = 1) r5, (SELECT t1.user_id, t1.rank, t1.max_score FROM (SELECT p.user_id, p.max_score, @curRank := @curRank + 1 AS rank FROM (SELECT user_id, MAX(score) AS max_score FROM game_instances WHERE country = 'cname' AND region = 'rname' GROUP BY user_id) p, (SELECT @curRank := 0) r ORDER BY p.max_score DESC) t1 WHERE t1.user_id = 1) r6 [/code]
Ich bin auf Linux Mint. Wenn ich versuche, Roboter für Robotframework in meiner Python -Anwendung zu importieren, wird sie nicht als installierte Bibliothek erkannt. Während ich die ausführbare Datei...
Ich kämpfe darum, das horizontale Bildschirm zwischen meinem festen Header und der Liste in React virtualisiert zu synchronisieren. Ich habe die ersten und letzten Spalten behoben und erfolgreich...
Ich kämpfe darum, das horizontale Bildschirm zwischen meinem festen Header und der Liste in React virtualisiert zu synchronisieren. Ich habe die ersten und letzten Spalten behoben und erfolgreich...
Ich kämpfe darum, das horizontale Bildschirm zwischen meinem festen Header und der Liste in React virtualisiert zu synchronisieren. Ich habe die ersten und letzten Spalten behoben und erfolgreich...
Mobile version