mysql - 如何在 CodeIgniter 中查找具有不同 where 条件的特定行和其他列的计数

Question

这是我的模型代码:

function getSaleOrderFormInfoDetail($saleOrderId)
{
$this->db->_protect_identifiers=false;

$this->db->select('BaseTbl.itemId, count(CASE WHEN SaleOrder.orderDate >= DATE_ADD(curdate(), INTERVAL -6 MONTH) AND SaleOrder.orderDate <= curdate() THEN SaleOrder.orderDate ELSE NULL END) as totalOrderLast6Month');
$this->db->from('tbl_saleorderdetail as BaseTbl');
$this->db->join('tbl_saleorders as SaleOrder', 'SaleOrder.saleOrderId = BaseTbl.masterId','left');
$this->db->where('BaseTbl.masterId', $saleOrderId);
$this->db->order_by("BaseTbl.id", "asc");
$query = $this->db->get();

return $query->result();
}

从此查询中，我获取销售订单的每个项目及其最近 6 个月的销售记录计数，其中包含固定的 where 子句 $this->db->where('BaseTbl.masterId', $saleOrderId);.我想要获取此 where 子句的项目，但我还需要过去 6 个月的所有销售订单记录计数。

Answer 1

尝试调用 getSaleOrderFormInfoDetail 两次。

第一次有条件，第二次无条件

function getSaleOrderFormInfoDetail($saleOrderId = '')
{
$this->db->_protect_identifiers=false;

$this->db->select('BaseTbl.itemId, count(CASE WHEN SaleOrder.orderDate >= DATE_ADD(curdate(), INTERVAL -6 MONTH) AND SaleOrder.orderDate <= curdate() THEN SaleOrder.orderDate ELSE NULL END) as totalOrderLast6Month');
$this->db->from('tbl_saleorderdetail as BaseTbl');
$this->db->join('tbl_saleorders as SaleOrder', 'SaleOrder.saleOrderId = BaseTbl.masterId','left');
if(!empty($saleOrderId)){
   $this->db->where('BaseTbl.masterId', $saleOrderId);
}
$this->db->order_by("BaseTbl.id", "asc");
$query = $this->db->get();

return $query->result();
}

现在就这样做

$counter = count($this->model_name->getSaleOrderFormInfoDetail());

这将计算行数

$data = $this->model_name->getSaleOrderFormInfoDetail($saleOrderId);

这将为您提供有条件的数据