Menu
I have been struggling on how to get the quantity of distinct values from a collection in Eloquent.
I have been trying several methods, such as unique(), values(), etc., found on the docs. Even though there is indeed a count() method, there is no method to get the count of distinct values.
For example, by applying the following query
$technicalshighestdegrees = Capsule::table('academicinfo AS fa')
->selectRaw('DISTINCT fa.academic_id AS Id,c.name AS Degree')
->leftJoin('academics AS a','fa.academic_id','=','a.id')
->leftJoin('cat_degree AS c','fa.level','=','c.id')
->whereIn('a.type',['Technical'])
->where('a.status','!=','Retired')
->where('c.degree',true)
->orderBy('a.id')
->orderBy('c.hierarchy','desc')/*http://stackoverflow.com/a/17006377/1883256*/
->get();
I get this collection:
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => stdClass Object
(
[Id] => 3
[Grado] => Master
)
[1] => stdClass Object
(
[Id] => 3
[Grado] => Bachelor
)
[2] => stdClass Object
(
[Id] => 4
[Grado] => Master
)
[3] => stdClass Object
(
[Id] => 4
[Grado] => Bachelor
)
[4] => stdClass Object
(
[Id] => 6
[Grado] => Master
)
[5] => stdClass Object
(
[Id] => 6
[Grado] => Bachelor
)
[6] => stdClass Object
(
[Id] => 18
[Grado] => Bachelor
)
[7] => stdClass Object
(
[Id] => 27
[Grado] => Bachelor
)
[8] => stdClass Object
(
[Id] => 34
[Grado] => Master
)
[9] => stdClass Object
(
[Id] => 34
[Grado] => Bachelor
)
[10] => stdClass Object
(
[Id] => 36
[Grado] => PhD
)
[11] => stdClass Object
(
[Id] => 36
[Grado] => Master
)
[12] => stdClass Object
(
[Id] => 36
[Grado] => Bachelor
)
[13] => stdClass Object
(
[Id] => 37
[Grado] => Bachelor
)
[14] => stdClass Object
(
[Id] => 42
[Grado] => PhD
)
[15] => stdClass Object
(
[Id] => 42
[Grado] => Master
)
[16] => stdClass Object
(
[Id] => 42
[Grado] => Bachelor
)
[17] => stdClass Object
(
[Id] => 50
[Grado] => Bachelor
)
[18] => stdClass Object
(
[Id] => 52
[Grado] => Bachelor
)
[19] => stdClass Object
(
[Id] => 53
[Grado] => Master
)
[20] => stdClass Object
(
[Id] => 53
[Grado] => Bachelor
)
[21] => stdClass Object
(
[Id] => 54
[Grado] => Master
)
[22] => stdClass Object
(
[Id] => 54
[Grado] => Bachelor
)
[23] => stdClass Object
(
[Id] => 55
[Grado] => Master
)
[24] => stdClass Object
(
[Id] => 55
[Grado] => Bachelor
)
[25] => stdClass Object
(
[Id] => 57
[Grado] => Bachelor
)
[26] => stdClass Object
(
[Id] => 68
[Grado] => Master
)
[27] => stdClass Object
(
[Id] => 68
[Grado] => Bachelor
)
[28] => stdClass Object
(
[Id] => 72
[Grado] => Master
)
[29] => stdClass Object
(
[Id] => 72
[Grado] => Bachelor
)
[30] => stdClass Object
(
[Id] => 77
[Grado] => Bachelor
)
[31] => stdClass Object
(
[Id] => 82
[Grado] => Bachelor
)
[32] => stdClass Object
(
[Id] => 85
[Grado] => PhD
)
[33] => stdClass Object
(
[Id] => 85
[Grado] => Master
)
[34] => stdClass Object
(
[Id] => 85
[Grado] => Bachelor
)
[35] => stdClass Object
(
[Id] => 92
[Grado] => Master
)
[36] => stdClass Object
(
[Id] => 92
[Grado] => Bachelor
)
[37] => stdClass Object
(
[Id] => 100
[Grado] => Master
)
[38] => stdClass Object
(
[Id] => 100
[Grado] => Bachelor
)
[39] => stdClass Object
(
[Id] => 111
[Grado] => Bachelor
)
[40] => stdClass Object
(
[Id] => 117
[Grado] => Master
)
[41] => stdClass Object
(
[Id] => 117
[Grado] => Bachelor
)
[42] => stdClass Object
(
[Id] => 123
[Grado] => Master
)
[43] => stdClass Object
(
[Id] => 123
[Grado] => Bachelor
)
)
)
Since, I just want to get the highest degrees (a user Id may have several historical degrees), I apply the unique() method, that according to the docs, it leaves only the first original value, in my case, I have already ordered them by hierarchy desc:
$technicalshighestdegrees = $technicalshighestdegrees->unique('Id');
And now I get the following collection (reduced to 25 items, which is exactly the total number):
Illuminate\Support\Collection Object
(
[items:protected] => Array
(
[0] => stdClass Object
(
[Id] => 3
[Degree] => Master
)
[2] => stdClass Object
(
[Id] => 4
[Degree] => Master
)
[4] => stdClass Object
(
[Id] => 6
[Degree] => Master
)
[6] => stdClass Object
(
[Id] => 18
[Degree] => Bachelor
)
[7] => stdClass Object
(
[Id] => 27
[Degree] => Bachelor
)
[8] => stdClass Object
(
[Id] => 34
[Degree] => Master
)
[10] => stdClass Object
(
[Id] => 36
[Degree] => PhD
)
[13] => stdClass Object
(
[Id] => 37
[Degree] => Bachelor
)
[14] => stdClass Object
(
[Id] => 42
[Degree] => PhD
)
[17] => stdClass Object
(
[Id] => 50
[Degree] => Bachelor
)
[18] => stdClass Object
(
[Id] => 52
[Degree] => Bachelor
)
[19] => stdClass Object
(
[Id] => 53
[Degree] => Master
)
[21] => stdClass Object
(
[Id] => 54
[Degree] => Master
)
[23] => stdClass Object
(
[Id] => 55
[Degree] => Master
)
[25] => stdClass Object
(
[Id] => 57
[Degree] => Bachelor
)
[26] => stdClass Object
(
[Id] => 68
[Degree] => Master
)
[28] => stdClass Object
(
[Id] => 72
[Degree] => Master
)
[30] => stdClass Object
(
[Id] => 77
[Degree] => Bachelor
)
[31] => stdClass Object
(
[Id] => 82
[Degree] => Bachelor
)
[32] => stdClass Object
(
[Id] => 85
[Degree] => PhD
)
[35] => stdClass Object
(
[Id] => 92
[Degree] => Master
)
[37] => stdClass Object
(
[Id] => 100
[Degree] => Master
)
[39] => stdClass Object
(
[Id] => 111
[Degree] => Bachelor
)
[40] => stdClass Object
(
[Id] => 117
[Degree] => Master
)
[42] => stdClass Object
(
[Id] => 123
[Degree] => Master
)
)
)
What I want is to get the quantities for each distinct degree value as follows:
Array
(
[Master] => 13
[Bachelor] => 9
[PhD] => 3
)
But in an Eloquent collection...
How can I achieve this?
490
Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows. SELECT COUNT(DISTINCT yourColumnName) AS anyVariableName FROM yourTableName; To understand the above syntax, let us create a table.
You can use KeyBy or pluck with same key-Value pair to pick Distinct Values. PHP array can only have unique keys. From this Idea we can get Unique models based on Array key.
To count distinct values, you can use distinct in aggregate function count(). The result i3 tells that we have 3 distinct values in the table.
In Laravel, the distinct() method is used to fetch distinct records from the database, it is also part of Laravel query builder, which means it can be chained to other query builder methods as well. The typical usage of this method is to find how many users made comments on a post.
Update for 2021/L6+
thanks @JeremyWadhams
$degrees->groupBy('Degree')->map(fn ($people) => $people->count());
// or using HigherOrder proxy on the collection
$degrees->groupBy('Degree')->map->count();
Using collection methods this would be it:
$degrees->groupBy('Degree')->map(function ($people) {
return $people->count();
});
// Collection {
// 'Master' => 13,
// 'Bachelor' => 9,
// 'PhD' => 3
// }
There is a Collection Helper called CountBy, does exactly what you need.
Collections CountBy
$degrees->countBy('Degree');
It will retourn as expected
Array
(
[Master] => 13
[Bachelor] => 9
[PhD] => 3
)
Simple :D
9
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
