拉勒维尔:如何计算会员数量与地位和购买的套餐

Question

如何计算会员人数与地位和购买的一揽子？

|                              Membership Status       |
|    Package Type    | Pending | Active | Cancellation |
| ------------------ | ------- | ------ | ------------ |
| Pearl Premier Full |    5    |   10   |       2      |
| Pearl Premier Odd  |    1    |   7    |       0      |
| Pearl Premier Even |    0    |   3    |       0      |

这是我的成员表：

| id | status  | package_id |
| -- | ------- | ---------- | 
| 1  | Active  |      3     |
| 2  | Pending |      1     |

public function package()
{
    return $this->belongsTo('App\Models\Package');
}

怎么算不。(团购套餐是旅游套餐，每个会员将购买一个套餐)

$data = \DB::table('members')->select(\DB::raw(
            'COUNT(CASE status WHEN "Pending" THEN 1 ELSE NULL END) AS pending,
            COUNT(CASE status WHEN "Active" THEN 1 ELSE NULL END) AS active,
            COUNT(CASE status WHEN "Cancellation" THEN 1 ELSE NULL END) AS cancelled'
        ))->get();

Answer 1

我不知道您所说的“和购买的包”是什么意思，但是如果您想用“是否”或“case条件”计数，可以将COUNT更改为SUM，并且必须对查询进行分组。

$data = \DB::table('members')->select(\DB::raw(
    'SUM(CASE status WHEN "Pending" THEN 1 ELSE 0 END) AS pending,
    SUM(CASE status WHEN "Active" THEN 1 ELSE 0 END) AS active,
    SUM(CASE status WHEN "Cancellation" THEN 1 ELSE 0 END) AS cancelled'
))->groupBy(colomn)->get();