Ad

Query That Brings The Most Occurrences Of A Certain Field In Laravel

I have the following query :

$top_cont = DB::table('quests') 
       ->whereBetween('created_at', [Carbon::now()->startOfDay(), Carbon::now()->endOfDay()])  
       ->take(5)
       ->get(); 

The quests table has a user_id column. I'm trying to get the top 5 user_ids who have the most contributions (i.e. has the most number of rows in the quests table for this day).

How can I adjust the query to bring the user_ids that have the most occurrences ?

If someone can do it in raw sql, this will be helpful too.

Ad

Answer

You could do something like this:

$top_cont = DB::table('quests') 
    ->select('user_id', DB::raw('count(*) as contributions'))
    ->whereDate('created_at', '>=', now()->startOfDay()) 
    ->take(5)
    ->groupBy('user_id')
    ->orderBy('contributions', 'desc')
    ->get(); 

This should give you 5 records with this form:

dd($top_cont);
=> Illuminate\Support\Collection {#3403
     all: [
       {#256
         user_id: 2,
         contributions: 51,
       },
       {#3417
         user_id: 975,
         contributions: 50,
       },
       {#3418
         user_id: 743,
         contributions: 46,
       },
       {#3419
         user_id: 538,
         contributions: 45,
       },
       {#3420
         user_id: 435,
         contributions: 18,
       },
     ],
   }
Ad
source: stackoverflow.com
Ad