Laravel – Best way to split/group a query to obtain range counts?

  laravel, mysql, php, sql

What i would like to achieve is to grab the the total count of comments made by users within different ranges (0-100, 100-200, 200-300) so i can use this within a graph.

The most naive way i can think is to to grab all of the users to begin with like so:

$users =  User::all();

And then loop over them

foreach ( $user as $user ){
    $total_comments_by_user = Comment::where('user_id', '=', $user->id)->count();

    if( $total_comments_by_user >= 400){
        $range400  += 1;
    } else if($total_comments_by_user >= 300){
        $range300  += 1;
    } else if($total_comments_by_user >= 200){
        $range200 += 1;
    } else if($total_comments_by_user >= 100){
        $range100  += 1;
    } else if($total_comments_by_user >= 0){
        $range0    += 1;
    }

}

Obviously this would be really bad and suffers from the N+1 problem which would progressively get worse as the number of users increases.

My idea then was to just group the query like so:

$comments = DB::table('comments')
             ->select(DB::raw('count(*) as comments_made'))
             ->groupBy('user_id')
             ->get();

And then perform a map or loop over and perform a similar task to before. This is a lot more efficient in terms of the number of queries performed as you only perform one query instead of potentially thousands, but it still feels poor.

Is there a way i can perform the range checks within the SQL query and get an array of the total comments made within ranges 0-100, 100-200, etc.

Source: Laravel

Leave a Reply