Request giving count of each distinct family

Question

I am looking for a sql request which will provide this kind of result.

id | family       result :
 1     A          [A => 2, B => 1, C => 1]
 2     A
 3     B
 4     C

Preferably using eloquent.

Current solution :

materials()->select('family', DB::raw('count(*) as total'))
           ->groupBy('family')
           ->pluck('total', 'family');

Have you already tried something? This is not a code-on-demand site... — Stefano Zanini, May 16 '17 at 08:38
A simple groupBy and count query would provide you with this. — Sandeesh, May 16 '17 at 08:39
The request I try doesn't work at all, so I don't wan't to lead people in a wrong direction — Neok, May 16 '17 at 08:40
Don't worry, anyone with some basic SQL knowledge won't be misled by your attempts — Stefano Zanini, May 16 '17 at 08:40
I see, you get the result the way i mentioned and perform a transformation on the collection to get this result. I'll whip up a code for you if you can't get it done. — Sandeesh, May 16 '17 at 08:42
Possible duplicate of [Laravel Eloquent groupBy() AND also return count of each group](http://stackoverflow.com/questions/18533080/laravel-eloquent-groupby-and-also-return-count-of-each-group) — Shadow, May 16 '17 at 08:43
I will look to the [pluck](https://laravel.com/docs/5.4/collections#method-pluck) function that Guillaume use and come back — Neok, May 16 '17 at 08:45
@Sandeesh the pluck did what I wan't, that the things I was missing. ty — Neok, May 16 '17 at 08:51

score 2 · Accepted Answer · answered May 16 '17 at 08:42

2

You can try something like:

$plop = Plop::selectRaw('family, COUNT(id) AS total')->groupBy('family')->pluck('total', 'family');

As you has not given enough details, it's hard to be precise.

answered May 16 '17 at 08:42

Guillaume

Thanks for the pluck function, I will look in this way – Neok May 16 '17 at 08:46
@ThomasPetillot This does exactly why you need – Sandeesh May 16 '17 at 08:49

score 0 · Answer 2 · answered May 16 '17 at 08:47

0

With laravel eloquent, using groupBy() and count():

$model=Model::get()->groupBy('family');
foreach($model as $key => $mod)
 {
        echo $key;
        echo (count($mod));
 }

answered May 16 '17 at 08:47

Sanzeeb Aryal

2 Answers2