php - Is it possible to count the groups after groupby statement in laravel?

Question

I have following table structure.

Students

• id

• first_name

• last_name

• roll_number

I have following query:

$query = Student::select(
'first_name',
'last_name', 
'roll_number', 
 DB::raw('CONCAT(first_name,last_name) as full_name')
)->groupBy('full_name');

What I am trying to get is to count rows with unique full_name.

When I append ->count() on above query it gives Column not found exception for full_name

My Approach:

$query = Student::select(
'first_name',
'last_name', 
'roll_number', 
 DB::raw('CONCAT(first_name,last_name) as full_name')
)->groupBy('full_name');

$count =  $query->get()->count();

It's giving me correct count but when I have large number of students in the database, there could be some performance issues since all students will be processed while counting collection $query->get().

Is there any way to achieve what I am trying to achieve ?

Suggestions are highly appreciated and please provide me the details if we can't achieve such thing in laravel.

question from:https://stackoverflow.com/questions/65842896/is-it-possible-to-count-the-groups-after-groupby-statement-in-laravel

Answer 1

You should just be aggregating by both the first and last name columns:

$query = Student::select(DB::raw('CONCAT(first_name, last_name) AS full_name'))
    ->groupBy('first_name', 'last_name');
$count = $query->get()->count();

Note that your current query is invalid, because you are grouping by the full name, but are selecting other non aggregate columns. In addition, it is dangerous to assume that the concatenation of the first and last name means what you think it does. Consider:

bo derick
bode rick

Both of these people (different) would have the same concatenated full name. Instead, aggregate by the first and last name columns separately.

Edit:

I don't know how Laravel's count() function be implemented under the hood, specifically whether or not it be smart enough to know to do the count on the MySQL server, rather than bringing the entire result set over to PHP and counting it. It might be beneficial to just do a count query directly on MySQL:

$sql = "SELECT COUNT(*) FROM (SELECT 1 FROM student GROUP BY first_name, last_name) t";
$count = Student::select(DB::raw($sql))->get();