php - 在 Laravel 中使用 SQL 进行连接内连接和记录计数

Question

我被 Laravel 项目的 SQL 查询难住了，所以首先我将描述这些关系。

cartages 具有以下字段:id、date_ending

cartage_batches 具有以下内容:id、cartage_id

cartage_items 具有以下内容:id、batch_id、amount

我希望能够做的是查询cartage表，将其分组为从date_weekending字段中提取的年份和月份。但还要获取每个月 cartages 中 cartage_batches 中 cartage_items 的计数。

我知道这会涉及到连接中的连接，但我不完全确定如何在 Laravel 中实现这一点。

示例数据:

Cartage

id   date_ending
1    2019-01-01
2    2019-01-08
3    2019-03-04

Cartage_Batches

id   cartage_id
1    1
2    1
3    1
4    2
5    2

Cartage_Items

id   batch_id   amount
1    1          20.00
2    2          5.00
3    2          15.00
4    4          13.00

预期输出

2019-01 => ['count' => 4],
2019-03 => ['count' => 0]

Answer 1

这是一个原始的 MySQL 查询，它应该生成您想要的结果:

SELECT
    DATE_FORMAT(c.date_ending, '%Y-%m') AS ym,
    COUNT(ci.batch_id) AS count
FROM Cartage c
LEFT JOIN Cartage_Batches cb
    ON cb.cartage_id = c.id
LEFT JOIN Cartage_Items ci
    ON ci.batch_id = cb.id
GROUP BY
    DATE_FORMAT(c.date_ending, '%Y-%m');

Demo

您的 Laravel 代码可能如下所示:

$result = DB::table('Cartage c')
    ->leftJoin('Cartage_Batches cb', 'cb.cartage_id', '=', 'c.id')
    ->leftJoin('Cartage_Itmes ci', 'ci.batch_id', '=', 'cb.id')
    ->groupBy(DB::raw("DATE_FORMAT(c.date_ending, '%Y-%m')"))
    ->select(DB::raw("DATE_FORMAT(c.date_ending, '%Y-%m'), COUNT(ci.batch_id) AS count"))
    ->get();