在Laravel中连接不同的表 [英] Joining distinct tables in 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屋!