如何在CodeIgniter中通过SQL实现多列计算生成suggest列

看起来你在CodeIgniter的数据表格查询里构造计算列时遇到了两个核心问题：PHP变量直接嵌入SQL的语法错误，以及GROUP BY子句中非聚合列的SQL规范问题。咱们一步步来修正：

问题分析 #

你的目标公式(a + (a / 25 * b)) - c对应到SQL里是sum(p.qty) + (sum(p.qty)/25 * $intvl) - sp.oh，但存在两个明显问题：

1. 直接在SELECT字符串里写$intvl会导致PHP变量无法正确解析（或被当作SQL字符串处理），同时存在SQL注入风险；
2. 因为你用了GROUP BY p.no_part，sp.oh作为非聚合列，在严格SQL模式下会抛出错误——除非每个no_part在stok_part表中只有唯一对应记录，否则需要用聚合函数（比如MAX()）包裹。

修正后的代码 #

private function _get_datatables_query(){
    $intvl = '2';
    $tgl_stok = '2019-09-30';
    
    // 1. 用查询绑定替换直接变量，避免注入，同时确保变量正确传递
    $this->db->select('
        p.hso, 
        p.no_part, 
        p.nama_part, 
        jml, 
        sp.oh, 
        (SUM(p.qty) + (SUM(p.qty)/25 * ?)) - MAX(sp.oh) AS suggest
    ', false); // 第二个参数false关闭自动转义，避免公式被破坏
    
    $this->db->from('penjualan p');
    $this->db->join('stok_part sp', 'sp.no_part = p.no_part', 'left');
    
    // 2. WHERE条件也用绑定方式，更安全且语法正确
    $this->db->where('sp.tgl = ?', $tgl_stok);
    $this->db->where('p.tgl BETWEEN DATE_SUB(?, INTERVAL ? DAY) AND ?', [$tgl_stok, $intvl, $tgl_stok]);
    
    $this->db->group_by('p.no_part');
    // ...其他代码...
    
    // 绑定$intvl到SELECT语句的占位符
    $this->db->bind($intvl);
}

关键改动说明 #

• 查询绑定：用?作为占位符，通过$this->db->bind()或where数组传递变量，既解决了变量解析问题，又彻底避免了SQL注入风险。
• 关闭自动转义：select()方法的第二个参数设为false，防止CodeIgniter自动给计算式添加不必要的引号，破坏公式结构。
• 聚合列处理：用MAX(sp.oh)代替直接的sp.oh，确保符合GROUP BY的SQL规范（如果每个no_part在stok_part中只有一条记录，MAX()不会改变结果）。
• 公式优先级：用括号明确包裹计算逻辑，确保SQL严格按照你期望的顺序执行计算。

额外优化：如果担心SUM(p.qty)为0导致除以25出错，可以添加NULLIF避免除以0的情况：

(SUM(p.qty) + (NULLIF(SUM(p.qty), 0)/25 * ?)) - MAX(sp.oh) AS suggest

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