CODEWITHSUNDEEP
sqllaravel
Tomas Lucena
Tomas Lucena

Reputation: 1526

Laravel query - count and group from 3 different tables

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
                    )

....

Upvotes: 1

Views: 173

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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.

Upvotes: 1

Related Questions