laravel - Eloquent 连接和求和

Question

我有 2 个表，并希望获得一个查询，该查询从表“项目”中获取所有列，并根据字段“类型”从表“发票”中获取几个总和。

projects
+----+--------+------+--------+
| ID | Address| Date | Other  |
+----+--------+------+--------+
|  1 | demo   | date | other  |
|  2 | demo2  | date2| other2 |

invoices
+----+---------+-----+--------+
| ID | proj_id | type| amount |
+--------------+-----+--------+
| 1  |   1     | a   |   10   |
| 2  |   1     | a   |   20   |
| 3  |   1     | b   |   10   |
| 4  |   1     | b   |   15   |
| 5  |   1     | c   |   5    |
| 6  |   2     | a   |   30   |
| 7  |   2     | a   |   5    |
| 8  |   2     | b   |   30   |
| 9  |   2     | c   |   5    |
| 10 |   2     | c   |   30   |

使用 Laravel Eloquent 我希望能够获得：

+----+---------+------+-------+---+---+---+
| ID | Address | Date | Other | a | b | c |
+----+---------+------+-------+---+---+---+
|  1 | demo    | date | other |30 |25 | 5 |
|  2 | demo2   | date2| other2|35 |30 |35 |

我被总和部分困住了，实际上是整个事情！

到目前为止，我有：

$projects = DB::table('projects')           
        ->leftJoin('invoices', 'projects.id', '=', 'invoices.project_id')
        ->select('projects.*', 'invoices.*')
        ->get();

这显然不是很远！任何帮助将不胜感激！

标签： laraveleloquent

Answer 1

您需要一个基本的数据透视查询。最简单的方法可能是通过原始选择：

$sql = "projects.*, ";
$sql .= "sum(case when invoices.type = 'a' then invoices.amount end) as a, ";
$sql .= "sum(case when invoices.type = 'b' then invoices.amount end) as b, ";
$sql .= "sum(case when invoices.type = 'c' then invoices.amount end) as c";

$projects = DB::table('projects')
    ->select(DB::raw($sql))
    ->leftJoin('invoices', 'projects.id', '=', 'invoices.project_id')
    ->groupBy('project.id')
    ->get();

这应该对应于以下原始 MySQL 查询：

SELECT
    p.*,
    SUM(CASE WHEN i.type = 'a' THEN i.amount END) AS a,
    SUM(CASE WHEN i.type = 'b' THEN i.amount END) AS b,
    SUM(CASE WHEN i.type = 'c' THEN i.amount END) AS c
FROM project p
LEFT JOIN invoices I
    ON p.id = i.project_id
GROUP BY
    p.id;