GroupBy from relations using laravel eloquent

Question

I have TypeOfVehicle model that has many Vehicle.
How can I group all vehicles by type of vehicle name with counts to get something like this

[
   'Sedan'     => 10,
   'SUV'       => 25,
   'Crossover' => 5
]

nothing to share. _TypeOfVehicle_ model has only name attribute and **hasMany** relation with _Vehicle_. _Vehicle_ model have vehicle_type_id, name and other attributes . There is no code because I don't know how to do this with eloquent — knubbe, Nov 15 '19 at 09:29

score 1 · Answer 1 · answered Nov 15 '19 at 09:26

1

You can use

$counts = DB::table('tablename')
                 ->select('TypeOfVehicle', DB::raw('count(*) as total'))
                 ->groupBy('TypeOfVehicle')
                 ->get();

answered Nov 15 '19 at 09:26

CHARITRA SHRESTHA

772
4
17

score 1 · Answer 2 · answered Nov 15 '19 at 09:29

1

Counting Related Models

If you want to count the number of results from a relationship without actually loading them you may use the withCount method, which will place a {relation}_count column on your resulting models.

$typeOfVehicles = App\TypeOfVehicle::withCount('vehicles')->get();

foreach ($typeOfVehicles as $typeOfVehicle) {
    echo $typeOfVehicle->vehicles_count;
}

answered Nov 15 '19 at 09:29

Karan

1,146
1
10
24

That can do the job but I was hoping that group By with some closure inside can do the trick – knubbe Nov 15 '19 at 09:33

score 1 · Answer 3 · answered Nov 15 '19 at 09:37

Try this it gives you your solution

$vehicle=Vehicle::all();
        $grouped = $vehicle->groupBy(function ($item, $key) {
            return substr($item['that_coloumn_name_like_type'], 0);
        });
        $groupCount = $grouped->map(function ($item, $key) {
            return collect($item)->count();
        });
//dd($groupCount);  to check it work correctly

score 1 · Accepted Answer · answered Nov 15 '19 at 09:58

I assume you have eloquent relation type in Vehicle model, e.g.:

public function type()
{
    return $this->belongsTo('\App\TypeOfVehicle');
}

So, you can get you data this way:

$result = Vehicle::select('vehicle_type_id', DB::raw('count(vehicle_type_id) as cnt'))
    ->with('type') // with your type relation
    ->groupBy('vehicle_type_id') // group by type of vehicle
    ->get() // get collection from database
    ->pluck('cnt', 'type.name') // get only needful data
    ->toArray(); // cast to array if necessary

GroupBy from relations using laravel eloquent

4 Answers4