CODEWITHSUNDEEP
phpmysqlsqllaraveleloquent
K1-Aria
K1-Aria

Reputation: 1156

How to groupby with count in laravel for multiple fields

I am using laravel 5.4. Imagine I want to show some car advertising. I will get all car records from a table and load them into my page. Now I want to show multiple categories with counts in a sidebar (car types, car colors, car fuels) and I want to show the count for every category.

For example: we have 10 car ads, two of them are red, three are green and five of them are white. I want to have something like this in a sidebar:

red(2)
green(3)
white(5)

I did it with SQL groupBy:

        $colors = DB::table('advertisments')->select('color_name', DB::raw('count(color_name) as total_color'))->groupBy('color_name')->get();
        $gearboxes = DB::table('advertisments')->select('gearbox', DB::raw('count(gearbox) as total_gearbox'))->groupBy('gearbox')->get();
        $param = ['colors' => $colors,'gearboxes' => $gearboxes];
        return view('ads' , compact('ads','param'));

However, I have tens of categories and I can't do it with one groupBy so the number of queries is going to be too high. Is there a way to do it with a single query or with less queries?

Upvotes: 0

Views: 4129

Answers (2)

Md Majadul Islam
Md Majadul Islam

Reputation: 125

$allColors = Model::withCount(['advertisments_model', 'more_model', '...'])

Upvotes: -1

techbech
techbech

Reputation: 3884

You could use an array for make it easy to run through your different columns.

$params = [];
$columns = [
    'colors' => 'color_name',
    'gearboxes' => 'gearbox',
    // put your other columns in this array
];

foreach($columns as $name => $column)
    $params[$name] = DB::table('advertisments')->select($column, DB::raw('count(' . $column . ') as total'))->groupBy($column)->get();

return view('ads' , compact('ads','param'));

Upvotes: 2

Related Questions