Блог

Я создаю небольшой поиск в своем Laravel 7.0 приложении. У меня есть две модели Project , Company которые many to many связаны с project_associate_company промежуточной таблицей.

Мои таблицы 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|
  

Модель компании:

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

Сводная таблица (таблица отношений):

 ******************** 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           |
|____________________________________________________________|
  

Теперь в моем контроллере у меня:

 $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();
  

Ожидаемые результаты:

 | 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      |
  

Но генерирование результатов:

 | 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      |
  

Поэтому всякий раз, когда я присоединяюсь к проектам, я получаю повторяющиеся проекты, которые добавляются несколько раз во время операции CRUD в соответствии с ролью и специализацией. Мне нужны DISTINCT проекты, в которых я мог бы суммировать их площадь и стоимость, чтобы я мог сортировать их. В настоящее время у меня разные projects_count и unique_projects_count

Я пытался сделать groupBy('companies.id')->groupBy('projects.id') , но результаты получаются неверными. Как я могу этого добиться?

Обновление: другой эквивалентный запрос mysql будет

 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;
  

Старый: эквивалентный запрос, который вы ищете в mysql,

 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
  

Соответствующий запрос laravel будет

 $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();