Joining distinct tables in Laravel

Question

I'm building a small search in my Laravel 7.0 application. I'm having two models Project and Company which is into many to many relationship with project_associate_company intermediate table.

My tables are Project

******************** projects ***********************
|                                                   |
| id |        name         | area | cost |      created_at     |   updated_at      |
|  1 | Development Project | 1461 | 243  |2018-09-17 21:42:41|2018-09-17 21:42:41|
|  2 | Testing Project     | 1500 | 200  |2018-09-18 21:42:41|2018-09-18 21:42:41|

Company Model:

******************** companies ***********************
|                                                    |
| id |     name     |    state   |  type   | created_at | updated_at |
|  1 | Demo company | Maharastra | Private | .....      | ....       |
|  1 | Test company |   Gujarat  | Public  | .....      | ....       |

Pivot table (relation) table:

******************** project_associate_company ***************
|                                                            |
| id | project_id | company_id | role_id | specialisation_id |
|  1 |     1      |     1      |   1     |       1           |
|  2 |     1      |     1      |   2     |       2           |
|  3 |     2      |     1      |   1     |       1           |
|  4 |     2      |     2      |   1     |       1           |
|  5 |     1      |     2      |   4     |       2           |
|____________________________________________________________|

Now in my controller I'm having:

$companies = Company::join('project_associate_company', function ($join) {
    $join->on('companies.id', '=', 'project_associate_company.company_id')
        ->whereNull('project_associate_company.deleted_at');
})
    ->join('projects', function ($join) {
        $join->on('project_associate_company.project_id', '=', 'projects.id')
            ->whereNull('projects.deleted_at');
    })
    ->select('companies.*',
        DB::raw('count(projects.id) as projects_count'),
        DB::raw('count(DISTINCT projects.id) as unique_projects_count'),
        DB::raw('SUM( projects.cost) as projects_cost'),
        DB::raw('SUM( projects.area) as projects_area')
    )
    ->groupBy('companies.id')
    ->orderBy('projects_area', 'desc')
    ->paginate();

Expected results:

| id |     name     | projects_count | unique_projects_count | projects_area | projects_cost |
|  1 | Demo company |        3       |           2           |     2961      |      443      |
|  1 | Test company |        2       |           2           |     2961      |      443      |

But results generating:

| id |     name     | projects_count | unique_projects_count | projects_area | projects_cost |
|  1 | Demo company |        3       |           2           |     4422      |      686      |
|  1 | Test company |        2       |           2           |     2961      |      443      |

So whenever I'm joining projects I'm getting duplicate projects which are added multiple during CRUD operation as per role and specialisation. I need to have DISTINCT projects where I can sum its area and cost, so that I can sort with them. Currently I'm having different projects_count and unique_projects_count

I tried doing groupBy('companies.id')->groupBy('projects.id') but the results are coming wrong. How can I achieve this?

Answer 1

Update: Another equivalent mysql query would be

SELECT 
    c.*,
   SUM(pc.projects_count) as projects_count,
    COUNT(p.id) as unique_projects_count,
   SUM(p.cost) as projects_cost,
   SUM(p.area) as projects_area
FROM companies c
INNER JOIN (
    SELECT company_id, project_id, COUNT(1) AS projects_count 
    FROM project_associate_company 
    WHERE deleted_at IS NULL
    GROUP BY company_id, project_id
) pc ON c.id = pc.company_id
INNER JOIN projects p ON pc.project_id = p.id
WHERE p.deleted_at IS NULL
GROUP BY c.id;

Old: The equivalent query you're looking for in mysql is

SELECT
    id,
    `name`,
    state,
    `type`,
    SUM(projects_count) projects_count,
    SUM(unique_projects_count) AS unique_projects_count,
    SUM(projects_cost) AS projects_cost,
    SUM(projects_area) AS projects_area
FROM (
    SELECT 
        c.*,
        COUNT(p.id) as projects_count,
        COUNT(DISTINCT p.id) as unique_projects_count,
       p.cost as projects_cost,
       p.area as projects_area
    FROM companies c
    INNER JOIN project_associate_company pc ON c.id = pc.company_id
    INNER JOIN projects p ON pc.project_id = p.id
    WHERE pc.deleted_at IS NULL AND p.deleted_at IS NULL
    GROUP BY c.id, p.id
    ) AS tmp
GROUP BY tmp.id

Corresponding laravel query would be

$innerQuery =   DB::table('companies as c')
                ->join('project_associate_company as pc', 'c.id', '=', 'pc.company_id')
                ->join('projects as p', 'pc.project_id', '=', 'p.id')
                ->select(DB::raw("c.id, 
                                 c.name, 
                                 c.state, 
                                 c.type, 
                                 COUNT(p.id) as projects_count, 
                                 COUNT(DISTINCT p.id) as unique_projects_count, 
                                 p.cost as projects_cost, 
                                 p.area as projects_area"))
                ->whereNull('pc.deleted_at')
                ->whereNull('p.deleted_at')
                ->groupByRaw('c.id, p.id');

$query =    DB::query()->fromSub($innerQuery, 't')
            ->select(DB::raw("
                        id,
                        `name`,
                        state,
                        `type`,
                        SUM(projects_count) projects_count,
                        SUM(unique_projects_count) AS unique_projects_count,
                        SUM(projects_cost) AS projects_cost,
                        SUM(projects_area) AS projects_area
                 "))
           ->groupBy('t.id')
           ->paginate()->toArray();