jqGrid: rebuild search filter options dynamically

Question

I have a jqGrid that gets data from a database using AJAX (in MVC). The rows and columns are dynamic and change based on what a user chooses from a select list, above the jqGrid. Each column has a drop down list box to filter the rows.

All is working great: Data is returned, columns and search filters are dynamically built and the filter works. There is one issue though:

If I filter a column using its search filter drop-down, after the rows have been filtered, the drop-down filters in the other columns still contain the original options that are no longer relevant to the current result set - e.g.

In a column that I haven't filtered, I may have only the following countries in the filtered results:

England
France
Italy

but the Search filter for that column still contains all countries, even though they are no longer relevant to the user:

Austria
Australia
Belgium
...
England
France
Italy
...
Taiwan
United States

I want the options in any filter drop-down that has not had a filter applied to it to only contain those relevant to the result set - in this case: England, France, Italy.

Is this possible? I have tried setting recreateFilter: true in the searchoptions but it made no difference.

It's my first time working with jqGrid so I've cobbled lots of code together from the web, which I'll post here if it helps anyone answer my question. One thing to note, I am using jqGrid 4.3.2 as I can't get later version to work. Anyway, here's the code and thanks for reading!

grid = $("#list");
grid.jqGrid({
            jsonReader: { repeatitems: true, root: "rows", page: "page", total: "total", records: "records" },
            url: '@Url.Content("~/DocumentSearch/GetData?keywordSearch=")' + keywords + '&documentTypeId=' + documentTypeId,
            datatype: 'json',
            mtype: 'GET',
            colModel: buildColumnModel(columns),
            rowList: [5, 10, 20, 50],
            viewrecords: true,
            height: 'auto',
            autowidth: true,
            loadonce: true,
            shrinktofit: true,
            rowNum: 10000,
            autoencode: true,
            autowidth: true,

            afterInsertRow: function (rowid, rowdata, rowelem) {
                $(this).attr("data-toggle", "modal");
                $(this).attr("data-target", ".bs-example-modal-sm");
            },
            beforeRequest: function () {
                var separator = ',';

                var i, l, rules, rule, parts, j, group, str, iCol, cmi, cm = this.p.colModel,
                                filters = $.parseJSON(this.p.postData.filters);


                if (filters && typeof filters.rules !== 'undefined' && filters.rules.length > 0) {
                    rules = filters.rules;

                    for (i = 0; i < rules.length; i++) {
                        rule = rules[i];
                        iCol = getColumnIndexByName.call(this, rule.field);
                        cmi = cm[iCol];


                        if (iCol >= 0 && ((typeof (cmi.searchoptions) === "undefined" ||
                                          typeof (cmi.searchoptions.sopt) === "undefined")
                                         && rule.op === myDefaultSearch) ||
                                            (typeof (cmi.searchoptions) === "object" &&
                                                $.isArray(cmi.searchoptions.sopt) &&
                                                cmi.searchoptions.sopt[0] === rule.op)) {


                            // make modifications only for the 'contains' operation
                            parts = rule.data.split(separator);

                            if (parts.length > 1) {
                                if (typeof filters.groups === 'undefined') {
                                    filters.groups = [];
                                }
                                group = {
                                    groupOp: 'OR',
                                    groups: [],
                                    rules: []
                                };

                                filters.groups.push(group);
                                for (j = 0, l = parts.length; j < l; j++) {
                                    str = parts[j];
                                    if (str) {
                                        // skip empty '', which exist in case of two separaters of once
                                        group.rules.push({
                                            data: parts[j],
                                            op: rule.op,
                                            field: rule.field
                                        });
                                    }
                                }
                                rules.splice(i, 1);
                                i--; // to skip i++
                            }
                        }
                    }

                    this.p.postData.filters = JSON.stringify(filters);
                }
            }
            , loadComplete: function (data) {
                var columnNamesArr = columns.split(',');
                for (i = 0; i < columnNamesArr.length; i++) {
                    setSearchSelect(columnNamesArr[i]);
                }

                grid.jqGrid('filterToolbar', {
                    search: true, searchOnEnter: true, autosearch: true, defaultSearch: myDefaultSearch
                });

                if (data != null && data.records != null) {
                    $("#recordCount").text(data.records.toString());
                }

                grid.trigger("reloadGrid");
            }
        });
    }



    function buildColumnModel(columns) {
        var uFields = columns.split(',');
        var columns = [];
        for (var i = 0; i < uFields.length; i++) {
            if (uFields[i].indexOf('Id') > -1) {
                columns.push({ name: uFields[i], index: uFields[i], hidden: true });
            }
            else {
                columns.push({ name: uFields[i], index: uFields[i] });
            }
        }

        return columns;
    }

    function setSearchSelect(columnName) {
        grid.jqGrid('setColProp', columnName, {
            stype: 'select',
            searchoptions: {
                sopt: ['eq', 'ne'],

                multipleSearch: true,
                multipleGroup: true,
                recreateFilter: true,
                closeOnEscape: true,
                closeAfterSearch: true,

                value: buildSearchSelect(getUniqueNames(columnName))
                , attr: { multiple: 'multiple', size: 2 }
                , dataInit: function (elem) {
                    setTimeout(function () {
                        $(elem).multiselect({
                            minWidth: 100,
                            height: "auto",
                            selectedList: 4,
                            checkAllText: "all",
                            uncheckAllText: "clear",
                            noneSelectedText: "All",
                            open: function () {
                                var $menu = $(".ui-multiselect-menu:visible");
                                $menu.width("auto");
                                return;
                            }
                        });
                    }, 50);
                }
            }
        });
    }

    function buildSearchSelect(uniqueNames) {
        var values = '';
        $.each(uniqueNames, function () {
            if (this.length > 0) {
                values += this + ":" + this + ";";
            }
        });

        if (values != null) {
            values = values.substr(0, values.length - 1);
        }

        return values;
    }

    function getUniqueNames(columnName) {
        var texts = grid.jqGrid('getCol', columnName);
        var uniqueTexts = [];
        var textsLength = texts.length;
        var text;
        var textsMap = {};
        var i;

        for (i = 0; i < textsLength; i++) {
            text = texts[i];
            if (text !== undefined && textsMap[text] === undefined) {
                // to test whether the texts is unique we place it in the map.
                textsMap[text] = true;
                uniqueTexts.push(text);
            }
        }

        uniqueTexts.sort();
        return uniqueTexts;
    }

adrianos · Accepted Answer

I solved this by hooking into dataEvents on the searchOptions, which as far as I can tell, is called during a change event of the the filter dropdown. I had to rebuild the unique values that would go into the filter dropdown for each column and then rebuild each html select:

function setSearchSelect(columnName) {
    grid.jqGrid('setColProp', columnName, {
        stype: 'select',
        searchoptions: {
            sopt: ['eq', 'ne'],
            value: buildSearchSelect(getUniqueNames(columnName))
            , attr: { multiple: 'multiple', size: 2 }
            , dataInit: function (elem) {
                setTimeout(function () {
                    $(elem).multiselect({
                        minWidth: 100,
                        height: "auto",
                        selectedList: 4,
                        checkAllText: "all",
                        uncheckAllText: "clear",
                        noneSelectedText: "All",
                        open: function () {
                            var $menu = $(".ui-multiselect-menu:visible");
                            $menu.width("auto");
                            return;
                        }
                    });
                }, 50);
            }
            , dataEvents: [
                {
                    type: "change",
                    fn: function (e) {
                        setTimeout(function () {
                            var columnNamesArr = columns.split(',');

                            for (i = 0; i < columnNamesArr.length; i++) {
                                if (columnNamesArr[i].toString() != columnName) {
                                    htmlForSelect = ""; // = '';
                                    var un = getUniqueNames(columnNamesArr[i]);
                                    var $select = $("select[id='gs_" + columnNamesArr[i] + "']");

                                    for (j = 0; j < un.length; j++) {
                                        val = un[j];

                                        htmlForSelect += '';
                                    }

                                    $select.find('option').remove().end().append(htmlForSelect);

                                    $select.multiselect('refresh');

                                    setTimeout(function () {
                                        $select.multiselect('destroy').multiselect({
                                            minWidth: 100,
                                            height: "auto",
                                            selectedList: 4,
                                            checkAllText: "all",
                                            uncheckAllText: "clear",
                                            noneSelectedText: "All",
                                            open: function () {
                                                var $menu = $(".ui-multiselect-menu:visible");
                                                $menu.width("auto");
                                                return;
                                            }
                                        });
                                    }, 50);
                                }
                            }
                        }, 500);
                    }
                }
            ]
        }
    });
}

If you have any questions, about this, I'll try my best to answer.

jqGrid: rebuild search filter options dynamically

Answers (2)

Related Questions