jqGrid: Multi-word search with global search

Question

Using phpGrid, I have created a single grid with a number of customizations, including a global search similar to Oleg's example here: Universal search field for jqgrid

Now, I am attempting to implement multi-word search (e.g. JQGrid Toolbar Searching: search for multiple words for a column) with the global search, but haven't been able to get it working. Submitting multiple words (i.e. tricolor rat terrier) should search all of these words. Submitting multiple words in quotes (i.e. "rat terrier") should search the exact term.

Note that I have not yet implemented highlighted search terms, however I do plan to.

Code snippet:

    // Add global search
$("#data_toppager_center table tbody tr").append($("<td><div class=\"fieldcontainer\"><input type=\"text\" name=\"gs\" id=\"gs\" class=\"searchfield\" placeholder=\"Keywords...\" tabindex=\"1\"><input type=\"submit\" name=\"gsbtn\" id=\"gsbtn\" value=\"\"></div></td>"));

var $grid = $("#data"); 
$("#gs").keypress(function (e) {
    var key = e.charCode || e.keyCode || 0;
    if (key === $.ui.keyCode.ENTER) { // 13
        $("#gsbtn").click();
    }
});
$("#gsbtn").button({
    text: false
}).click(function () {
    var postData = $grid.jqGrid("getGridParam", "postData"),
    colModel = $grid.jqGrid("getGridParam", "colModel"),
    rules = [],
    searchText = $("#gs").val(),
    l = colModel.length,
    i,
    cm;
    for (i = 0; i < l; i++) {
        cm = colModel[i];
        if (cm.search !== false && (cm.stype === undefined || cm.stype === "text")) {
            rules.push({
                field: cm.name,
                op: "cn",
                data: searchText
            });
        }
    }
    postData.filters = JSON.stringify({
        groupOp: "OR",
        rules: rules
    });
    $grid.jqGrid("setGridParam", { search: true });
    $grid.trigger("reloadGrid", [{page: 1, current: true}]);
    return false;
});

Intendant

Answer 1

I think that one can relatively easy combine two solutions which I suggested before. The resulting demo allows to make global search (search in any searchable column) for multi-word text (multiple values divided by space separator):

or

The full code which I used in the demo is below:

$(function () {
    "use strict";
    var mydata = [
            { id: "1",  invdate: "2007-10-21", name: "test",   note: "3note",   amount: "200.00", tax: "10.00", closed: true,  ship_via: "TN", total: "210.00" },
            { id: "2",  invdate: "2007-10-22", name: "test2",  note: "3note2",  amount: "300.00", tax: "20.00", closed: false, ship_via: "FE", total: "320.00" },
            { id: "3",  invdate: "2007-09-01", name: "test3",  note: "3note3",  amount: "400.00", tax: "30.00", closed: false, ship_via: "FE", total: "430.00" },
            { id: "4",  invdate: "2007-10-14", name: "test4",  note: "3note4",  amount: "200.00", tax: "10.00", closed: true,  ship_via: "TN", total: "210.00" },
            { id: "5",  invdate: "2007-10-31", name: "test5",  note: "3note5",  amount: "300.00", tax: "20.00", closed: false, ship_via: "FE", total: "320.00" },
            { id: "6",  invdate: "2007-09-06", name: "test6",  note: "3note6",  amount: "400.00", tax: "30.00", closed: false, ship_via: "FE", total: "430.00" },
            { id: "7",  invdate: "2007-10-04", name: "test7",  note: "3note7",  amount: "200.00", tax: "10.00", closed: true,  ship_via: "TN", total: "210.00" },
            { id: "8",  invdate: "2007-10-03", name: "test8",  note: "3note8",  amount: "300.00", tax: "20.00", closed: false, ship_via: "FE", total: "320.00" },
            { id: "9",  invdate: "2007-09-22", name: "test9",  note: "3note9",  amount: "400.00", tax: "30.00", closed: false, ship_via: "TN", total: "430.00" },
            { id: "10", invdate: "2007-09-08", name: "test10", note: "3note10", amount: "500.00", tax: "30.00", closed: true,  ship_via: "TN", total: "530.00" },
            { id: "11", invdate: "2007-09-28", name: "test11", note: "3note11", amount: "500.00", tax: "30.00", closed: false, ship_via: "FE", total: "530.00" },
            { id: "12", invdate: "2007-09-10", name: "test12", note: "3note12", amount: "500.00", tax: "30.00", closed: false, ship_via: "FE", total: "530.00" }
        ],
        $grid = $("#list"),
        initDatepicker = function (elem) {
            $(elem).datepicker({
                //dateFormat: "dd-M-yy",
                autoSize: true,
                changeYear: true,
                changeMonth: true,
                showButtonPanel: true,
                showWeek: true
            });
        },
        numberTemplate = {formatter: "number", align: "right", sorttype: "number",
            editrules: {number: true, required: true},
            searchoptions: { sopt: ["eq", "ne", "lt", "le", "gt", "ge", "nu", "nn", "in", "ni"] }},
        highlightFilteredData = function () {
            var $self = $(this), filters, i, l, rules, rule, iCol,
                isFiltered = $self.jqGrid("getGridParam", "search"),
                postData = $self.jqGrid("getGridParam", "postData"),
                colModel = $self.jqGrid("getGridParam", "colModel"),
                colIndexByName = {};

            // validate whether we have input for highlighting
            if (!isFiltered || typeof postData !== "object") {
                return;
            }
            filters = $.parseJSON(postData.filters);
            if (filters == null || filters.rules == null || filters.rules.length <= 0) {
                return;
            }

            // fill colIndexByName which get easy column index by the column name
            for (i = 0, l = colModel.length; i < l; i++) {
                colIndexByName[colModel[i].name] = i;
            }

            rules = filters.rules;
            for (i = 0, l = rules.length; i < l; i++) {
                rule = rules[i];
                iCol = colIndexByName[rule.field];
                if (iCol !== undefined) {
                    $self.find(">tbody>tr.jqgrow>td:nth-child(" + (iCol + 1) + ")").highlight(rule.data);
                }
            }
        };

    $grid.jqGrid({
        datatype: "local",
        data: mydata,
        colNames: ["Client", "Date", "Amount", "Tax", "Total", "Closed", "Shipped via", "Notes"],
        colModel: [
            { name: "name", width: 65, editrules: {required: true} },
            { name: "invdate", width: 80, align: "center", sorttype: "date",
                formatter: "date", //formatoptions: { newformat: "d-M-Y" },
                searchoptions: { sopt: ["eq", "ne", "lt", "le", "gt", "ge"], dataInit: initDatepicker } },
            { name: "amount", width: 75, template: numberTemplate },
            { name: "tax", width: 52, template: numberTemplate },
            { name: "total", width: 60, template: numberTemplate },
            {name: "closed", width: 70, align: "center", formatter: "checkbox",
                edittype: "checkbox", editoptions: {value: "Yes:No", defaultValue: "Yes"},
                stype: "select", searchoptions: { sopt: ["eq", "ne"], value: ":Any;true:Yes;false:No" } },
            {name: "ship_via", width: 105, align: "center", formatter: "select",
                edittype: "select", editoptions: { value: "FE:FedEx;TN:TNT;IN:Intim", defaultValue: "IN" },
                stype: "select", searchoptions: { sopt: ["eq", "ne"], value: ":Any;FE:FedEx;TN:TNT;IN:Intim" } },
            { name: "note", width: 60, sortable: false, search: false, edittype: "textarea" }
        ],
        rowNum: 10,
        rowList: [5, 10, 20],
        toolbar: [true, "top"],
        pager: "#pager",
        gridview: true,
        rownumbers: true,
        autoencode: true,
        ignoreCase: true,
        sortname: "invdate",
        viewrecords: true,
        sortorder: "desc",
        shrinkToFit: false,
        height: "100%",
        caption: "Demonstrate how to implement global multi-word searching (with blank separator)",
        loadComplete: function () {
            highlightFilteredData.call(this);
        }
    }).jqGrid("navGrid", "#pager", {add: false, edit: false, del: false, search: false});

    // fill top toolbar
    $('#t_' + $.jgrid.jqID($grid[0].id))
        .append($("<div><label for=\"globalSearchText\">Global search in grid for:&nbsp;</label><input id=\"globalSearchText\" type=\"text\"></input>&nbsp;<button id=\"globalSearch\" type=\"button\">Search</button></div>"));
    $("#globalSearchText").keypress(function (e) {
        var key = e.charCode || e.keyCode || 0;
        if (key === $.ui.keyCode.ENTER) { // 13
            $("#globalSearch").click();
        }
    });
    $("#globalSearch").button({
        icons: { primary: "ui-icon-search" },
        text: false
    }).click(function () {
        var postData = $grid.jqGrid("getGridParam", "postData"),
            colModel = $grid.jqGrid("getGridParam", "colModel"),
            rules = [],
            searchText = $("#globalSearchText").val(),
            l = colModel.length,
            separator = ' ',
            searchTextParts = $.trim(searchText).split(separator),
            cnParts = searchTextParts.length,
            i,
            iPart,
            cm;
        for (i = 0; i < l; i++) {
            cm = colModel[i];
            if (cm.search !== false && (cm.stype === undefined || cm.stype === "text")) {
                for (iPart = 0; iPart < cnParts; iPart++) {
                    rules.push({
                        field: cm.name,
                        op: "cn",
                        data: searchTextParts[iPart]
                    });
                }
            }
        }
        postData.filters = JSON.stringify({
            groupOp: "OR",
            rules: rules
        });
        $grid.jqGrid("setGridParam", { search: true });
        $grid.trigger("reloadGrid", [{page: 1, current: true}]);
        return false;
    });
});

UPDATED: To allow to use quoted strings with spaces in the global searching string you can use match instead of split. The modified demo allows to use the following

Dovecote

Thank you, Oleg. Using your example, I have the multi-word search and keyword highlighting implemented and working. However, it doesn't appear that this code will support the second requirement: submitting multiple words in quotes (i.e. "rat terrier") should search the exact term. Did I miss something? – Intendant

@sheldonbeswift: See UPDATED part of my answer with the modified demo. – Dovecote

I have a new challenge - I've added an "Export to Excel" option, which works perfectly when using Advanced Search and Integrated Search, but not when using Global Search as it creates a very, very long and ill-formatted request string (e.g. /export.php?dt=json&gn=orgvdc&export_type=EXCEL&_search=true&nd=1398191649622&rows=25&page=1&sidx=Org_Site&sord=asc&filters=**%7B"groupOp"%3A"OR"%2C"rules"%3A%5B%7B"field"%3A"ID"%2C"op"%3A"cn"%2C"data"%3A"Test"%7D%2C%7B"field"%3A"OID"%2C"op"%3A"cn"%2C"data"%3A"Test"%7D%2C%7B"**[and so on...]&oper=excel"). Can you help? – Intendant

@sheldonbeswift: Sorry, but it's new question which has no relation with the previous one. What is the problem? Do you can export to Excel, but have problem if the searching rule is too long? – Dovecote

I spent some more time looking at it and have confirmed that it is a phpGrid bug: When "groupOp":"OR", export fails. The same request works as expected when "groupOp":"AND". – Intendant

@sheldonbeswift: Sorry, but I don't use phpGrid, jqGrid PHP, jqSuite PHP or any other commercial products which are based on jqGrid. I use pure free open source jqGrid written in JavaScript. I use on the server side C# and ASP.NET. If you found a bug it would be better if you report it to the developers of the product which you use. – Dovecote

Thank you, I've already submitted a ticket with phpGrid. I'm sorry that I wasn't clear about that in my comment. Thank you for your help, we'll see what they come back with. – Intendant