Getting unique values GROUPBY, DISTINCT, UNIQUE - what to choose? PHP Laravel

Question

What I'm trying to do is to get top 5 player scores from database. I'm keeping player data in 3 tables, so it's more complicated.

Right now I'm getting scores, but having problem with making them unique per user.

In top 5, one player can appear only once (even if he has top 5 scores on own)

Current query:

DB::table('gameplays', 'gameplay')
            ->join('users', 'gameplay.user_id', '=', 'users.id')
            ->join('playerdatas', 'gameplay.user_id', '=', 'playerdatas.user_id')
            ->select([ DB::raw('DISTINCT(gameplay.user_id)'), 'users.name', 'gameplay.score', 'gameplay.duration', 'gameplay.created_at', 'playerdatas.appearance'])
            ->where('gameplay.created_at', '>', Carbon::now()->subDays($type))
            ->where('gameplay.game_id', '=', $game_id)
            ->orderBy('gameplay.score', 'DESC')
            ->limit(5)
            ->get();

So DISTINCT not work for me, getting all scores not unique per user.

Tryied also with:

->distinct('gameplay.user_id')
->groupBy('gameplay.user_id')
->unique('gameplay.user_id')

It was working with ->unique('gameplay.user_id') on collection but I would like to make that in query not on collection, because I'm currenlty limiting results to 5, so I would miss some records.

Tables:

games
|id|name |
|1 |game1|

users
|id|name|
| 1|test|
| 2|elo |
| 3| me |
| 4| 3f2|
| 5| 123|

playerdatas
|id|user_id|game_id|appereance|
| 1| 1     |     1 |  null    |
| 1| 2     |     1 |  null    |
| 1| 3     |     1 |  null    |
| 1| 4     |     1 |  null    |
| 1| 5     |     1 |  null    |

gameplays
|id|game_id|score|created_at|user_id|
| 1| 1     | 100 |24.07.2020| 1     |
| 1| 1     | 700 |24.07.2020| 1     |
| 1| 1     | 400 |24.07.2020| 2     |
| 1| 1     | 300 |24.07.2020| 3     |
| 1| 1     | 200 |24.07.2020| 1     |
| 1| 1     | 400 |24.07.2020| 2     |
| 1| 1     | 500 |24.07.2020| 3     |
| 1| 1     | 100 |24.07.2020| 2     |
| 1| 1     | 200 |24.07.2020| 3     |
| 1| 1     | 100 |24.07.2020| 1     |
| 1| 1     | 900 |24.07.2020| 2     |
| 1| 1     | 870 |24.07.2020| 3     |

It would be great if you could add the various tables in the post? — Aashish gaba, Jul 24 '20 at 12:36
If it was me, I'd start with the raw sql. With that in mind, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Jul 24 '20 at 14:12

user3532758 · Answer 1 · 2020-07-26T10:42:15.477

You can use group by in a subquery to group the users based on the total scores. Then join that to playerdata to get information from that table. Here is an example (you can add where conditions based on your requirement):

First, the subquery bit:

$scores = DB::table('gameplays')
            ->select(DB::raw('MAX(score) as max_score'), //edited after your comment
                   DB::raw('gameplays.user_id',  //edited aft. your comment
                   DB::raw('users.name as user_name')))
          ->join('users', 'users.id', 'gameplays.user_id')
          ->groupBy('gameplays.user_id')
          ->orderBy('max_score', 'desc') //edited aft. comment
          ->limit(5); //limit to 5 players

Now get playerdata information and use joinSub to join the subquery ($scores):

$playerData = DB::table('playerdatas')
              ->joinSub($scores, 'max_scores', function($join){ //edited to max_scores
                   $join->on('max_scores.user_id', 'playerdatas.user_id');
              })
              ->orderBy('max_score', 'desc') //edited aft comment
              ->get();

The same in raw query would be:

select * from `playerdatas` 
inner join 
 (select MAX(score) as max_score, 
 gameplays.user_id from `gameplays` 
 inner join `users` on `users`.`id` = `gameplays`.`user_id` 
 group by `gameplays`.`user_id` 
 order by `max_score` desc   
 limit 5) as `max_scores` on `max_scores`.`user_id` = `playerdatas`.`user_id` 
order by `max_score` desc

Thanks for that answer, it didn't actually do what I wanted to do but that was close. I don't want to have sum of scores, just MAX score of every player and create ranking on that. To make final query working I needed to set mysql strict_mode to false, due to sql_mode=only_full_group_by — Michał Lipa, Jul 25 '20 at 21:36
Oh, I misunderstood your logic. But, I think the same query should work without turning changing full group by settings; you would just need to substitute `sum` with `max`.. — user3532758, Jul 26 '20 at 10:29
Oh wait, my select is `users.id as user_id,` it should be `gamplays.user_id` and you dont need to alias it either, my mistake. If you changed that to `gamplays.user_id` you wouldnt need to change full group by settings. — user3532758, Jul 26 '20 at 10:32

score 1 · Answer 2 · answered Jul 24 '20 at 17:30

User Model

public function playerData(){
   return hasOne(App\PlayerData::class, 'user_id', 'id');
}

GamePlay Model

public function game(){
   return belongsTo(App\Game::class, 'game_id', 'id');
}

public function user(){
   return belongsTo(App\User::class, 'user_id', 'id');
}

Get all the gameplays for the game with given game_id and that satisfies the created_at condition(you mentioned in the post).

$gamePlays = GamePlay::with(['game', 'user.playerData'])
   ->where('game_id', $game_id)
   ->where('created_at', '>', Carbon::now()->subDays($type))
   ->get();

Now we'll use methods available on collection. (orderByDesc, groupBy).

https://laravel.com/docs/7.x/collections#available-methods

Will contain all the user details required along with their top scores.

$users = collect([]);

foreach($gamePlays->groupBy('user_id') as $user_id => $gp_c){

    // $gp_c are the gameplays of that user from the above list that we obtained.

    // From those gameplays get the one with the highest score.

    $gp = $gp_c->sortByDesc('score')->first();
    
    // add the necessary details.
    $users.push([
         'id' => $user_id,
         'name' => $gp->user->name,
         'score' => $gp->score,
         'duration' => $gp->duration,
         'created_at' => $gp->created_at,
         'appearance' => $gp->user->playerData->appearance,
    ]);
}

Select the top 5(or less in case the number of users is < 5 ) users with highest score.

$top_users = $users->sortByDesc('score')->take( (sizeof($users) > 5) ? 5 : sizeof($users)));

Thanks for that answear, I think that might work well. I didn't go that way, because I think that would be less effecient than raw sql, but someday maybe I'll try that out and check how I'll work on real data — Michał Lipa, Jul 25 '20 at 21:40
Yes, I agree with you, raw sql would be more efficient than this. But Eloquent ORM has its own beauty :) And even in the solution I just have one DB query, rest are all collection methods. Thanks :) — Aashish gaba, Jul 25 '20 at 22:16

score 0 · Accepted Answer · answered Jul 25 '20 at 21:55

Writing answer to let you know what I finally did to sort that problem.

I've created raw SQL in laravel with GROUP BY, but that wasn't all. The problem was that is you using GROUP BY now (latest versions of MySQL) you have to either add all selected columns to GROUP BY or aggregated them by one of functions (MIN, MAX, SUM, etc.). If you don't do that you will get error:

this is incompatible with sql_mode=only_full_group_by

source: How can I solve incompatible with sql_mode=only_full_group_by in laravel eloquent?

So to bypass that error and make GROUP BY work for that case I had to switch in laravel database.php mysql mode to not strict mode

 'mysql' => [
            'strict' => false,

This is not ideal solution, only temporary one for now, but it works as expected.

Getting unique values GROUPBY, DISTINCT, UNIQUE - what to choose? PHP Laravel

3 Answers3