Laravel query - count and group from 3 different tables

Question

I am giving my first step in LARAVEL and SQL and I am trying to build a query that returns the list of departments I have and, at the same time, count the total of locations in each department and the total of device of each location.

So I tried the following:

$dep = DB::table('departments')
       ->leftjoin('locations','departments.dep_id','=','locations.department')
       ->leftjoin('devices','locations.id','=','devices.location')
       ->select('departments.name',DB::raw('count(locations.id) as locationcount'))
       ->groupby('departments.name')
       ->get();

This return me the following:

Illuminate\Support\Collection Object
(
    [items:protected] => Array
        (
            [0] => stdClass Object
                (
                    [name] => Interior Design
                    [locationcount] => 3
                )

            [1] => stdClass Object
                (
                    [name] => IT
                    [locationcount] => 29
                )

            [2] => stdClass Object
                (
                    [name] => Marketing
                    [locationcount] => 0
                )

            [3] => stdClass Object
                (
                    [name] => Operations
                    [locationcount] => 13
                )

        )

)

But here the count we are seeing is for the devices and no for the locations. Is there any way to achieve this query? or I need to do a loop after? I am looking for a result like this:

Illuminate\Support\Collection Object
    (
        [items:protected] => Array
            (
                [0] => stdClass Object
                    (
                        [name] => Interior Design
                        [locationcount] => 2
                        [devicecount] => 10
                    )

....

Can you add some sample data? may be database column with data for each table and what do you want as end results? — Sohel0415
– Sohel0415, Commented Jul 29, 2019 at 11:01

Tim Biegeleisen · Accepted Answer · 2019-07-29 11:07:00Z

1

Include a count for the devices, and then modify your current logic to take the distinct location count:

$dep = DB::table('departments')
    ->leftjoin('locations', 'departments.dep_id', '=', 'locations.department')
    ->leftjoin('devices', 'locations.id', '=', 'devices.location')
    ->select('departments.name',
             DB::raw('COUNT(DISTINCT locations.id) AS locationcount'),
             DB::raw('COUNT(devices.id) AS devicecount'))
    ->groupby('departments.name')
    ->get();

The reason this should work is that the pure device count should already reflect the true count since it uses the last table in the join. For the locations, we take the distinct count to undo the double counting which may occur due to joining to the devices table.

answered Jul 29, 2019 at 11:07

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

Tomas Lucena Over a year ago

mate I love you, this is perfect!

Collectives™ on Stack Overflow

Laravel query - count and group from 3 different tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related