How to groupby with count in laravel for multiple fields

Question

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?

That does not matter, i just want to do that. But I think it's only possible with sql and group by — K1-Aria
– K1-Aria, Commented Oct 3, 2017 at 7:41

techbech · Accepted Answer · 2017-10-03 07:51:28Z

2

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'));

answered Oct 3, 2017 at 7:51

techbech

3,9145 gold badges25 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

K1-Aria Over a year ago

thank you . its less code but it is still tens of queries. is there any way to Decrease queries??

techbech Over a year ago

I don't believe as long it's different columns. You could use subqueries, but those still count as a query. You will lose the waiting for the respond of the database every time, but aslong your database is on the same server, or the same datacenter as your webserver, I don't see a huge possible performance boost.

K1-Aria Over a year ago

Ok so i will do this by multiple group by and i think its best way and not bad

techbech Over a year ago

Super, will you accept my answer then? A thing you could do, is to structure your table in a different way, but that is ofcourse a lot more work.

techbech Over a year ago

If you had a relation table with your properties, containing a column to tell what the property is, like "color", and then have a column with the value, then you could group by both columns. In that way you will get all counts in one query. But this ofcourse also means that your properties need the same column-type for your values.

Md Majadul Islam · Accepted Answer · 2021-01-19 09:02:49Z

-1

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

answered Jan 19, 2021 at 9:02

Md Majadul Islam

1251 silver badge11 bronze badges

4 Comments

Mark Rotteveel Over a year ago

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes.

Ray Coder Over a year ago

How to grouping it with advertisments_model?

Md Majadul Islam Over a year ago

@MarkRotteveel Thanks to advice me

Md Majadul Islam Over a year ago

@RayCoder Thanks to ask me to the main point. Laravel help to us in buildin method with eloquent relationship it set in advertisements model like this : public function advertisements(){ return $this->belongsTo(product::class, 'id', 'product_id'); }

Collectives™ on Stack Overflow

How to groupby with count in laravel for multiple fields

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related