代码之家 › 专栏 › 技术社区 › Nitish Kumar

在Laravel中连接不同的表

eloquent laravel

Nitish Kumar · 技术社区 · 5 年前

Laravel 7.0 应用我有两个模特 Project Company 这是一个 many to many project_associate_company 中间表格。

我的桌子是 项目

******************** 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') 但结果是错误的。我怎样才能做到这一点?

0 回复 | 直到 5 年前

Dark Knight 5 年前

使现代化 :另一个等效的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

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