总和左联接多个表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

相关推荐