Eloquent query to count unique column

How would I query for a return count of unique column values?

So if my users have an attribute of color with optional values of red, blue, and green.

I want to return:

red => 15 blue => 33 green => 23

I can do this easily with individual queries such as:

Model::where('this', 'that')->count();

But I want to return all of them at once.

Thanks.

Level 73

This should do the trick.

DB:select(SELECT color, count(color) quantity FROM table GROUP BY color);

You can probably do it with eloquent all the way but this is a SQL solution.

Best Answer

Level 24

With Eloquent:

Model::select('color', DB::raw('count(color) quantity'))->groupBy('color')->get();

5 likes

Level 24

Thanks for suggestions, both worked!

Ultimately, I used the following flow:

In Controller:

$fleets = Fleet::select('id', 'reg', 'model')->get();

In View:

@foreach($fleets->groupBy('model')->map(function($values) {return $values->count(); }) as $key => $jet)

Please or to participate in this conversation.