如何在jqGrid中从MySQL拉取数据填充select下拉选择框并实现联动

代码存在的问题 #

• 行内编辑场景下，给smallCategoryZone下拉框赋值时使用append()方法，没有先清空原有选项，多次切换largeCategoryZone选项会导致小分类选项重复累加
• 重复获取行ID：先通过closest('tr.jqgrow')拿到当前行ID后，又用getGridParam('selrow')覆盖了ID值，当操作非选中行的下拉选项时，会把小分类填充到错误的行中
• 缺少异常兜底：没有处理AJAX请求失败、返回数据为空的场景，此时小分类下拉框会保持旧值或者无选项，没有提示
• 初始化jqGrid时postData里的certificateNumber只取了一次初始值，后续输入框值变更后重新加载网格时，提交的还是旧的查询值

修正后完整代码 #

$(document).ready(function () {
    $("#AdminCheckJqGrid").jqGrid({
        url: "../barcode/barcodeAdminCheckInquiry/1",
        datatype: "json",
        loadtext: "loading...",
        method: "GET",
        // 动态获取certificateNumber值，避免传旧值
        postData: {
            "certificateNumber": function() {
                return $("#certificateNumber").val();
            }
        },
        colNames: ['sku', 'label', 'qty', 'realQty', 'palletType', 'palletBarcode','largeCategoryZone','middleCategoryZone','smallCategoryZone', 'remark'],
        colModel: [
            {label: 'sku', name: 'sku', width: '30', align: 'center'},
            {label: 'label', name: 'label', width: '60', align: 'center'},
            {label: 'qty', name: 'qty', width: '20', align: 'center'},
            {label: 'realQty', name: 'realQty', width: 30,align: 'center', sortable: false, editable: true},
            {label: 'palletType', name: 'palletType', width: 40, align: 'center', editable: true, edittype: 'select', editoptions: {dataUrl: '../code/palletType', buildSelect: setSelect}},
            {label: 'palletBarcode', name: 'palletBarcode', width: 40, align: 'center', editable: true, edittype: 'select', editoptions: {dataUrl: '../code/palletBarcode', buildSelect: palletBarcodeSelect}},
            {label: 'largeCategoryZone', name: 'largeCategoryZone', width: 40, align: 'center', editable: true, edittype: 'select', editoptions: {dataUrl: '../code/largeCategoryZone', buildSelect:setSelect ,
                    dataEvents: [{
                        type: 'change',
                        fn: function (e) {
                            var selectedData = e.currentTarget.value;
                            var dataFrist = selectedData.substr(0,1);
                            var dataAscii = dataFrist.charCodeAt();

                            $.ajax({
                                url: '../code/largeCategoryZone/' + dataAscii,
                                type: 'GET',
                                success: function (data) {
                                    var jdata = jQuery.parseJSON(data).result || [];
                                    // 新增默认空选项，避免误选
                                    var selectHtml = "<option value=''>请选择小分类</option>";

                                    for(var i=0; i < jdata.length; i++){
                                        selectHtml += "<option value='" + jdata[i].subcode + "'>" + jdata[i].codename + "</option>";
                                    }

                                    if ($(e.target).is('.FormElement')) {
                                        // 表单编辑场景
                                        var form = $(e.target).closest('form.FormGrid');
                                        $("select#smallCategoryZone.FormElement", form[0]).html(selectHtml);
                                    } else {
                                        // 行内编辑场景，直接用当前行的ID，不使用selrow覆盖避免填错行
                                        var row = $(e.target).closest('tr.jqgrow');
                                        var rowId = row.attr('id');
                                        // 先清空再赋值，避免选项累加
                                        jQuery("select#" + rowId + "_smallCategoryZone").html(selectHtml);
                                    }
                                },
                                error: function() {
                                    // 请求失败兜底提示
                                    var emptyHtml = "<option value=''>小分类加载失败</option>";
                                    if ($(e.target).is('.FormElement')) {
                                        var form = $(e.target).closest('form.FormGrid');
                                        $("select#smallCategoryZone.FormElement", form[0]).html(emptyHtml);
                                    } else {
                                        var row = $(e.target).closest('tr.jqgrow');
                                        var rowId = row.attr('id');
                                        jQuery("select#" + rowId + "_smallCategoryZone").html(emptyHtml);
                                    }
                                }
                            });
                        }}]}},
            {label: 'middleCategoryZone', name: 'middleCategoryZone', width: 40, align: 'center', editable: true, edittype: 'select', editoptions: {dataUrl: '../code/largeCategoryZone', buildSelect: setSelect}},
            {label: 'smallCategoryZone', name: 'smallCategoryZone', width: 40, align: 'center', editable: true, edittype: 'select',editoptions: {  } },
            {label: 'remark', name: 'remark', width: 50, sortable: false, editable: true, align: 'center', editoptions: {autocomplete: "off"}},
        ],
    });
});

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