总和左联接多个表laravel6 [英] sum left join multiple tables laravel6
问题描述
我有3个表,表的薪水(id,name)和点数(id,datep,salarie_id,已售出)和avances(id,montantA,salarie_id).
I have 3 tables,table salaries(id, name) and pointages(id, datep, salarie_id, sold) and avances(id,montantA, salarie_id).
表格工资
Id name
1 kamal
2 imad
积分表
id datep Salarie_id sold
1 11/03/2020 1 120
2 05/03/2020 1 100
3 06/03/2020 2 50
4 07/03/2020 2 20
搜索表
id datea montantA Salarie_id
1 11/03/2020 120 1
2 02/03/2020 50 1
通过这3个表,我想像这样恢复salaries.id和salaries.name和总和(pointages.sold)和总和(avances.montantA)
by these 3 tables I want to recover salaries.id and salaries.name and sum (pointages.sold) and sum (avances.montantA) like this
Salarie.id Salaries.nom Sum(sold) Sum(montantA)
1 kamal 220 170
2 imad 70 0
当我执行mysql请求时,它会给我一个确切的结果:
when I execute the mysql request it gives me an exact result:
SELECT
s.id AS 'Salarie.id',
s.nom AS 'Salarie.nom',
COALESCE(p.somme, 0) AS 'SUM(sold)',
COALESCE(a.somme, 0) AS 'SUM(montantA)'
FROM
Salaries s
LEFT JOIN (SELECT SUM(sold) AS somme, salarie_id FROM pointages GROUP BY salarie_id) AS p ON p.salarie_id = s.id
LEFT JOIN (SELECT SUM(montantA) AS somme, salarie_id FROM avances GROUP BY salarie_id) AS a ON a.salarie_id = s.id
GROUP BY s.id, p.salarie_id, a.salarie_id
现在我想在SalarieController的状态函数中编写此查询,但我不知道如何
now i want to write this query in a state function of SalarieController and I don't know how
推荐答案
您的原始sql可以由查询生成器使用,如下所示:
Your raw sql can be use by query-builder like this:
$p = DB::table('pointages')
->groupBy('salarie_id')
->selectRaw('SUM(sold) AS somme, salarie_id');
$a = DB::table('avances')
->groupBy('salarie_id')
->selectRaw('SUM(montantA) AS somme, salarie_id');
DB::table('Salaries AS s')
->leftJoin(DB::raw("({$p->toSql()}) AS p"), 'p.salarie_id', '=', 's.id')
->leftJoin(DB::raw("({$a->toSql()}) AS a"), 'a.salarie_id', '=', 's.id')
->groupBy('s.id', 'p.salarie_id', 'a.salarie_id')
->selectRaw('s.id AS "Salarie.id",
s.nom AS "Salarie.nom",
COALESCE(p.somme, 0) AS SUM(sold),
COALESCE(a.somme, 0) AS SUM(montantA)')
->get();
或者您可以在不使用子查询的情况下使用leftjoin:
Or you can use leftjoin without subquery:
DB::table('Salaries AS s')
->leftJoin('pointages AS p', 'p.salarie_id', '=', 's.id')
->leftJoin('avances AS a', 'a.salarie_id', '=', 's.id')
->groupBy('s.id')
->selectRaw('s.id AS "Salarie.id",
s.name AS "Salaries.nom",
SUM(p.sold),
SUM(a.montantA)')
->get();
这篇关于总和左联接多个表laravel6的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!