CodeIgniter 4中关联另一表统计订单状态对应行数的实现

需求 #

从OrderIDStates表获取排除OrderStateID=0（Closed）的所有状态，关联orders表统计每个状态对应的订单行数，无对应订单时amount显示为0。

表结构 #

orders表 #

OrderIDStates表 #

当前代码问题 #

现有代码仅返回有订单的状态，无法保留无对应订单的状态：

return $this->db->table('OrderIDStates')
->where('OrderStateID >', '0') // 不显示State为0（Closed）的项
->join('orders', 'orders.State = OrderIDStates.OrderStateID', 'left')
->select('OrderStateID, OrderStateText')
->selectCount('State', 'amount')
->groupBy('State')
->orderBy('OrderIDStates.OrderStateID', 'asc')
->get()
->getResult();

当前返回结果 #

{"OrderStates":[
{"OrderStateID":"1","OrderStateText":"Open","amount":"1"},
{"OrderStateID":"2","OrderStateText":"Pending","amount":"1"}
]}

期望返回结果 #

{"OrderStates":[
{"OrderStateID":"1","OrderStateText":"Open","amount":"1"},
{"OrderStateID":"2","OrderStateText":"Pending","amount":"1"},
{"OrderStateID":"3","OrderStateText":"Wait for Parcel","amount":"0"},
{"OrderStateID":"4","OrderStateText":"Delivered","amount":"0"},
{"OrderStateID":"5","OrderStateText":"recall","amount":"0"}
]}

解决方案 #

问题核心在于分组字段错误：左连接后无订单的行orders.State为null，按State分组会将这些行合并，导致状态丢失。需按主表OrderIDStates.OrderStateID分组，并确保计数为0时正确显示。

修正后的代码：

return $this->db->table('OrderIDStates')
    ->where('OrderStateID >', 0) // 排除Closed状态
    ->join('orders', 'orders.State = OrderIDStates.OrderStateID', 'left')
    ->select('OrderIDStates.OrderStateID, OrderStateText')
    ->select('COALESCE(COUNT(orders.State), 0) AS amount')
    ->groupBy('OrderIDStates.OrderStateID, OrderStateText')
    ->orderBy('OrderIDStates.OrderStateID', 'asc')
    ->get()
    ->getResult();

关键修改点 #

1. 分组字段调整：将groupBy('State')改为groupBy('OrderIDStates.OrderStateID, OrderStateText')，确保每个状态独立成组，不会丢失无订单的状态。
2. 计数处理：用COALESCE(COUNT(orders.State), 0)替代selectCount，明确处理无匹配订单时的0值（COUNT(orders.State)本身会忽略null值返回0，加上COALESCE更稳妥）。
3. 字段明确性：指定OrderIDStates.OrderStateID避免字段歧义，提升代码严谨性。

内容的提问来源于stack exchange，提问作者Rocky