Filter duplicates by field max value

user_5914044 0
Viewed 305

I have a problem with getting data. I'm trying to get all users with there max value (points). My question is just like OData filter by max value and field.

Code for my question is:

 $query = User::select('users.id','users.username','games.points', 
 'games.created_at')
            ->join('games', 'users.id', '=', 'games.user_id')
            ->whereBetween('games.created_at', [$start, $end]);

But the problem is when I want to remove duplicates and get max points for each user.

In the version where I don't need whereBetween I'm doing in this way:

 return User::select('users.id', 'users.username', 'games.points', 
                     'games.created_at')
            ->join('games', 'users.id', '=', 'games.user_id')
            ->whereRaw('(users.id, games.points) IN (SELECT user_id, MAX(points)                
                        FROM games GROUP BY user_id)')
            ->orderByDesc('points')
            ->get();

Thanks

laravel-query-builder eloquent laravel mysql
2 Answers

Why don't you simply GROUP BY your query on users.id and users.username?

-- I let you translate the query into your framework
SELECT users.id, users.username, max(games.points) as points
FROM users, games
WHERE users.id = games.user_id
AND games.created_at BETWEEN $start AND $end
GROUP BY users.id, users.username
ORDER BY max(games.points) DESC

That way, you'll get the max of points per user (id and username).

Hope this helps.

user_23033340

I have resolved my problem with using unique method form eloquent.

Game::with('user')
            ->whereBetween('created_at', [$start, $end])
            ->orderByDesc('points')
            ->orderBy('created_at')
            ->limit($limit)
            ->get()
            ->unique('user_id')
            ->values();

Thanks a lot to Lucas for his time and his help.

user_59140440
Related