CODEWITHSUNDEEP
mysqllaravel
Ben
Ben

Reputation: 75

Get top best score in Laravel

I have 2 tables:

user: id, name

score: id, user_id, point

Now I want to get 5 users name who have the best score but seem like it was wrong.

Here's my code:

public function getTop(){
        $top = DB::table('score')
            ->select('user_id', DB::raw('COUNT(point)'))
            ->groupBy('user_id')
            ->orderBy(DB::raw('COUNT(point)'), 'DESC')
            ->take(5)
            ->get();
        return view('home',compact('top'));
    }

Upvotes: 1

Views: 1289

Answers (2)

A.khalifa
A.khalifa

Reputation: 2496

Try this.

DB::table('users')
    ->select(['users.id', DB::raw('MAX(sc.point) AS score')])
    ->join('score AS sc', 'sc.id', '=', 'users.id')
    ->groupBy('users.id')
    ->take(5)->get();

Upvotes: 2

Rutvij Kothari
Rutvij Kothari

Reputation: 1285

In your case Database query makes more senses.

  1. Database query to get top 5 user_id with total score.

  2. Join users table with that result.

    $topResult = DB::table('users'
)->join(DB::raw('(SELECT user_id, SUM(point) as score FROM score GROUP BY user_id ORDER BY SUM(point) LIMIT 5) as top_scorer'), function($join) {
    $join->on('top_scorer.user_id', '=','users.id');
})
->select(['users.*', 'top_scorer.score']) //Select fields you need.
->orderBy('top_scorer.score')
->get();

Upvotes: 2

Related Questions