MySQL Datatables服务器端Coalesce函数报错及适配问题求助

问题回顾 #

你需要实现的功能是：展示mainproduk表的所有产品数据，关联rincian_order表计算omzet（公式为sum(rincian_order.jumlah_pc)/6），当没有关联数据时omzet填充为0。目前遇到两个问题：

• 直接使用coalesce函数时触发MySQL错误1054 Unknown column '0)' in 'field list'
• 之前可用的子查询写法在Datatables服务器端模式下无法生效

错误原因分析 #

1. Coalesce语法错误：你写的coalesce (sum(rincian_order.jumlah_pc)/6), 0) 括号位置错误，coalesce的第二个参数0应该放在函数的括号内部，正确写法是coalesce(sum(rincian_order.jumlah_pc)/6, 0)。错误的写法让MySQL误以为0)是一个列名，所以抛出1054错误。
2. Left Join失效：你把rincian_order的过滤条件直接写在where子句中，这会导致left join的效果丢失——没有关联rincian_order数据的mainproduk行被过滤掉了，因为where子句会要求rincian_order的条件必须满足，哪怕是null值也会被排除。

解决方案 #

针对这两个问题，我们需要做两处关键调整：

• 修正coalesce的语法，确保参数位置正确
• 将rincian_order的过滤条件移到join的on子句中，保留left join的特性，确保主表所有行都能被返回
• 清理select语句中重复的字段（比如重复多次的mainproduk.nama_alias），避免冗余

完整修正后的代码 #

模型查询代码

$this->datatables->select("
    mainproduk.id as id_m,
    mainproduk.barcode as barcod,
    mainproduk.nomor_kemtan as nomor_kemtan,
    mainproduk.nama_produk as nama_produk,
    mainproduk.satuan as satuan,
    mainproduk.nama_alias as nama_alias,
    mainproduk.produk_jadi as produk_jadi,
    mainproduk.min_stok_kemasan as min_stok_kemasan,
    mainproduk.tipe_produk as tipe_produk,
    mainproduk.top_item as top_item,
    mainproduk.status as status,
    coalesce(sum(rincian_order.jumlah_pc)/6, 0) as omzet
");
$this->datatables->from("mainproduk");
// 将rincian_order的过滤条件移到join的on子句中，保证left join生效
$this->datatables->join(
    "rincian_order", 
    "mainproduk.barcode = rincian_order.barcode 
     AND rincian_order.tipe = 'po' 
     AND rincian_order.status != 'canceled' 
     AND rincian_order.tanggal_kirim >= '2017-11-01' 
     AND rincian_order.tanggal_kirim <= '2018-04-30'", 
    "left"
);
$this->datatables->where("mainproduk.status =", "1");
$this->datatables->group_by("mainproduk.id");
$this->datatables->order_by("mainproduk.id", "ASC");
$this->datatables->add_column(
    "view", 
    "<a href='editproduk/$1'><span class='glyphicon glyphicon-edit' aria-hidden='true'></span></a> | <a href='logproduk/$1'><span class='fa fa-fw fa-history'></span></a>", 
    "id_m"
);
return $this->datatables->generate();

控制器代码（无需修改）

function json() {
    header('Content-Type: application/json');
    echo $this->ceklisqc_model->json();
}

Datatables前端脚本（清理重复列）

注意你原来的columns配置中有多个重复的"data": "nama_alias"，这里建议清理掉冗余列，避免表格显示重复内容：

$(function () {
    $.fn.dataTableExt.oApi.fnPagingInfo = function(oSettings) {
        return {
            "iStart": oSettings._iDisplayStart,
            "iEnd": oSettings.fnDisplayEnd(),
            "iLength": oSettings._iDisplayLength,
            "iTotal": oSettings.fnRecordsTotal(),
            "iFilteredTotal": oSettings.fnRecordsDisplay(),
            "iPage": Math.ceil(oSettings._iDisplayStart / oSettings._iDisplayLength),
            "iTotalPages": Math.ceil(oSettings.fnRecordsDisplay() / oSettings._iDisplayLength)
        };
    };
    var t = $("#example1").dataTable({
        initComplete: function() {
            var api = this.api();
            $('#example1_filter input')
                .off('.DT')
                .on('keyup.DT', function(e) {
                    if (e.keyCode == 13) {
                        api.search(this.value).draw();
                    }
                });
        },
        oLanguage: {
            sProcessing: "loading..."
        },
        processing: true,
        serverSide: true,
        ajax: {"url": "json", "type": "POST"},
        columns: [
            { "data": "id_m", "orderable": false },
            {"data": "barcod"},
            {"data": "nomor_kemtan"},
            {"data": "nama_produk"},
            {"data": "satuan"},
            {"data": "nama_alias"},
            {"data": "produk_jadi"},
            {"data": "omzet"},
            {"data": "min_stok_kemasan"},
            {"data": "tipe_produk"},
            {"data": "top_item"},
            {"data": "status"},
            {"data": "view"}
        ],
        order: [[1, 'asc']],
        rowCallback: function(row, data, iDisplayIndex) {
            var info = this.fnPagingInfo();
            var page = info.iPage;
            var length = info.iLength;
            var index = page * length + (iDisplayIndex + 1);
            $('td:eq(0)', row).html(index);
        }
    });
});

为什么之前的子查询写法不生效？ #

Datatables服务器端模式下，查询构造器（比如你用的$this->datatables）对嵌套子查询的支持有限，子查询的别名可能在后续的排序、分页处理中被解析错误，导致无法正常生成SQL。改用带条件的left join写法更符合查询构造器的处理逻辑，也能保证功能正常实现。

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