在Laravel中连接不同的表 [英] Joining distinct tables in Laravel
本文介绍了在Laravel中连接不同的表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在我的 Laravel 7.0 应用程序中进行小型搜索.我有两个模型 Project 和 Company ，它们与 project_associate_company 中间表成多对多关系./p>
我的表是 Project
********************项目***********************|||id |名称|面积|费用|created_at |Updated_at ||1 |开发项目|1461 |243 | 2018-09-17 21:42:41 | 2018-09-17 21:42:41 ||2 |测试项目1500 |200 | 2018-09-18 21:42:41 | 2018-09-18 21:42:41 | 公司型号:
********************公司***********************|||id |名称|州|类型created_at |Updated_at ||1 |演示公司|马哈拉施特拉邦|私人|..... |.... ||1 |测试公司|古吉拉特邦|公开|..... |.... | 数据透视表(关系)表:
<代码> ******************** 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'，函数($ 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');})->选择("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 |名称|projects_count |unique_projects_count |projects_area |projects_cost ||1 |演示公司|3 |2 |2961 |443 ||1 |测试公司|2 |2 |2961 |443 | 但是结果生成:
|id |名称|projects_count |unique_projects_count |projects_area |projects_cost ||1 |演示公司|3 |2 |4422 |686 ||1 |测试公司|2 |2 |2961 |443 | 因此，每当我加入项目时，我都会得到重复的项目，这些重复的项目在CRUD操作期间会根据角色和专业进行添加.我需要有 DISTINCT 个项目，在这里可以汇总其面积和成本，以便可以对它们进行排序.目前，我有不同的 projects_count 和 unique_projects_count
我尝试做 groupBy('companies.id')-> groupBy('projects.id')，但结果出了问题.我该如何实现?
解决方案
更新:另一个等效的mysql查询是
SELECTC.*，SUM(pc.projects_count)作为projects_count，COUNT(p.id)作为unique_projects_count，SUM(p.cost)作为projects_cost，SUM(p.area)作为projects_area来自公司c内部联接 (SELECT company_id，project_id，COUNT(1)个AS projects_count来自project_associate_companyWHERE Deleted_at IS NULLGROUP BY company_id，project_id)pc ON c.id = pc.company_idINNER JOIN项目p ON pc.project_id = p.id在哪里p.deleted_at IS NULLGROUP BY c.id; 旧:您正在mysql中寻找的等效查询是
SELECTID，名字状态，类型SUM(projects_count)projects_count，SUM(unique_projects_count)AS unique_projects_count，SUM(projects_cost)AS projects_cost，SUM(projects_area)AS项目_区域从 (选择C.*，COUNT(p.id)个为projects_count，COUNT(DISTINCT p.id)作为unique_projects_count，p.cost as projects_cost，p.area as projects_area来自公司c内联接project_associate_company pc ON c.id = pc.company_idINNER JOIN项目p ON pc.project_id = p.id其中pc.deleted_at为NULL和p.deleted_at为NULLGROUP BY c.id，p.id)AS tmp按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.type，COUNT(p.id)个为projects_count，COUNT(DISTINCT p.id)作为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，名字状态，类型SUM(projects_count)projects_count，SUM(unique_projects_count)AS unique_projects_count，SUM(projects_cost)AS projects_cost，SUM(projects_area)AS项目_区域"))-> groupBy('t.id')-> paginate()-> toArray(); 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?
解决方案
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();
这篇关于在Laravel中连接不同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
