CodeIgniter项目中将MySQL查询统计逻辑重构至PHP端实现

1. 简化模型层SQL查询 #

移除SQL层的聚合、递归逻辑，仅查询需要的原始基础数据，避免数据库端的复杂计算：

function get_report($start_date = '2021-09-25', $end_date = '2021-10-15'){
    // 只查询原始商品数据
    $listings = $this->db->query("SELECT * FROM listings WHERE added_date <= ?", [$end_date])->result();
    // 只查询对应时间范围内的状态日志
    $logs = $this->db->query("SELECT refno, status_from, logtime FROM logs WHERE logtime >= ? ORDER BY logtime ASC", [$start_date])->result();
    
    // 预处理日志为按商品编号分组的结构，方便后续查询
    $log_map = [];
    foreach ($logs as $log) {
        if (!isset($log_map[$log->refno])) {
            $log_map[$log->refno] = [];
        }
        $log_map[$log->refno][] = $log;
    }
    
    // PHP生成日期范围，替代SQL递归
    $dates = [];
    $current = strtotime($start_date);
    $end = strtotime($end_date);
    while ($current <= $end) {
        $dates[] = date('Y-m-d', $current);
        $current = strtotime('+1 day', $current);
    }
    
    // 遍历日期和商品，计算每天各状态数量
    $result = [];
    foreach ($dates as $day) {
        $day_ts = strtotime($day);
        $stat = [
            'day' => $day,
            'draft' => 0,
            'action' => 0,
            'publish' => 0,
            'sold' => 0,
            'let' => 0
        ];
        foreach ($listings as $listing) {
            // 过滤added_date大于当前日期的商品
            if (strtotime($listing->added_date) > $day_ts) {
                continue;
            }
            // 取当前日期商品的最新状态
            $current_status = $listing->status;
            if (isset($log_map[$listing->refno])) {
                foreach ($log_map[$listing->refno] as $log) {
                    if (strtotime($log->logtime) >= $day_ts) {
                        $current_status = $log->status_from;
                        break;
                    }
                }
            }
            // 对应状态计数+1
            switch (strtoupper($current_status)) {
                case 'D': $stat['draft']++; break;
                case 'A': $stat['action']++; break;
                case 'Y': $stat['publish']++; break;
                case 'S': $stat['sold']++; break;
                case 'L': $stat['let']++; break;
            }
        }
        $result[] = (object)$stat;
    }
    return $result;
}

2. 简化视图层代码 #

因为所有计算逻辑已经在模型层完成，视图层直接遍历输出即可：

<?php foreach($data_total as $row ): ?>
<tr>
    <td><?= $row->day ?></td>
    <td><?= $row->draft ?></td>
    <td><?= $row->publish ?></td>
    <td><?= $row->action ?></td>
    <td><?= $row->sold ?></td>
    <td><?= $row->let ?></td>
</tr>
<?php endforeach; ?>

方案优势 #

• 避免了数据库端的递归、笛卡尔积、多重子查询，降低数据库压力
• PHP端的计算逻辑可灵活调整，不需要修改SQL语句
• 数据查询逻辑拆分更清晰，后续维护成本更低
• 性能优于原SQL方案，尤其是数据量较大时优势更明显

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