laravel-5 - Laravel 使用 groupBy 选择计数

Question

我试图获取每个活动的 gift_code 表中唯一批次的计数。gift_code 表通过campaign_id 连接到campaign 表。

以下是活动表的一些示例数据。

--------------+--------------
|campaign_id | 姓名 |
--------------+--------------
| 1 | 美国广播公司 |
--------------+--------------
| 2 | xyz |
--------------+--------------

下面是gift_code 表的一些示例数据。

--------------+------------+---------- ----+
|gift_code_id | 活动 ID | 批次 | 唯一码 |
--------------+-------------+----------+--------- -----
| 1 | 1 | 1 | zxc23 |
--------------+-------------+----------+--------- ----+
| 2 | 1 | 2 | rtc26 |
--------------+-------------+----------++--------- ----+
| 3 | 2 | 1 | z8723 |
--------------+-------------+----------+--------- ----+
| 4 | 2 | 2 | h7c26 |
--------------+-------------+----------++--------- ----+
| 5 | 2 | 2 | rrcf6 |
--------------+-------------+----------++--------- ----+
| 6 | 2 | 3 | r7y28 |
--------------+-------------+----------++--------- ----+
| 7 | 2 | 3 | bnc26 |
--------------+-------------+----------++--------- ----+

 $campaign = DB::table('campaign')
                    ->select('campaign.*', DB::raw('count(gift_code.batch) as batch_count')->groupBy('gift_code.campaign_id')->groupBy('gift_code.batch'))
                    ->leftjoin('gift_code', 'campaign.campaign_id', '=', 'gift_code.campaign_id')
                    ->get();

我的预期结果是：

--------------+--------------+
|campaign_id | 名称 |batch_count|
--------------+-------------+------------+
| 1 | 美国广播公司 | 2 |
--------------+-------------+------------+
| 2 | xyz | 3 |
--------------+-------------+------------+

标签： laravel-5

Answer 1

试试下面的查询

$data = \DB::table('campaign as c')
       ->leftJoin('gift_code as gc','c.campaign_id','=','gc.campaign_id')
       ->select('c.*',\DB::raw('COUNT(distinct(gc.batch)) as batch_count'))
       ->groupBy('c.campaign_id')
       ->get();