Custom filtering searching Operation

Introduction. Searching and Filtering in jqGrid

jqGrid support searching/filtering out of the box. One can use filterToolbar to add the searching toolbar to the grid and one can use searchGrid method of navGrid for searching (the dialog which allows to search in the data).

If the user type in the searching toolbar and press Enter or of the user created some searching criteria and click "Find" button jqGrid set search: true option of jqGrid, set filters property of postData and reload the grid. jqGrid send filters property to the server in case of usage datatype: "json" or datatype: "xml" or implements the filtering of data locally in case of datatype: "local".

The internal structure of filters property of postData is like on the example below

{
    groupOp: "OR", // or "OR"
    rules: [
        {field: "name", op: "eq", data: "test1"}
    ],
    groups: [
         {
             groupOp: "AND",
             rules: [
                 {field: "name", op: "eq", data: "test2"},
                 {field: "ship_via", op: "ne", data: "FE"}
             ],
             groups: [...]
         }
     ]
}

which means the filter name='test1' OR (name='test2' AND ship_via<>'FE').

The state of the problem in jqGrid 4.7 and earlier version

The problem is: the list of operations in jqGrid till version 4.7 is predefined and can't be extended. Moreover some operations are not allows (or are implemented incorrectly) in case of local filtering/searching. The list of posible values of op property used in rules of filters is the following

• "eq" - "equal". Used with all typed of data for local and remote searching.
• "ne" - "not equal". Used with all typed of data for local and remote searching.
• "lt" - "less". Used mostly with numeric or date types of data for local and remote searching.
• "le" - "less or equal". Used mostly with numeric or date types of data for local and remote searching.
• "gt" - "greater". Used mostly with numeric or date types of data for local and remote searching.
• "ge" - "greater or equal". Used mostly with numeric or date types of data for local and remote searching.
• "bw" - "begins with". Used with string types of data for local and remote searching.
• "bn" - "does not begin with". Used with string types of data for local and remote searching.
• "in" - "is in". Used for remote searching only. The local implementation just use "eq" operation instead.
• "ni" - "is not in". Used for remote searching only. The local implementation just use "ne" operation instead.
• "ew" - "ends with". Used with string types of data for local and remote searching.
• "en" - "does not end with". Used with string types of data for local and remote searching.
• "cn" - "contains". Used with string types of data for local and remote searching.
• "nc" - "does not contain". Used with string types of data for local and remote searching.
• "nu" - "is null". Used with all types of data for local and remote searching. It's important that the data be correctly loaded in the grid and null values are not replaced to empty string for example.
• "nn" - "is not null". Used with all types of data for local and remote searching. It's important that the data be correctly loaded in the grid and null values are not replaced to empty string for example.

The array $.jgrid.search.odata or $.jgrid.search.locales.localeName.odata (where localeName is en-US, de or other locale defined typically in grid.locale-XX.js file) defines the names of operations (the text "begins with" which corresponds to operation "bw").

The method filterToolbar supports additionally short operands name for above operations. The operation operands of filterToolbar specifies the names. One can use $.jgrid.search.operands, or searching.operands parameter of jqGrid to specify another values of operations. The default values of operations displayed in filterToolbar are the following:

• "eq" - "=="
• "ne" - "!"
• "lt" - "<"
• "le" - "="
• "gt" - ">"
• "ge" - ">="
• "bw" - "^"
• "bn" - "!^"
• "in" - "="
• "ni" - "!="
• "ew" - "|"
• "en" - "!@"
• "cn" - "~"
• "nc" - "!~"
• "nu" - "#"
• "nn" - "!#"

Thus tree elements defines the searching/filter operation:

• the operation name, like "eq"
• the operand name, like "=="
• the text displayed in Searching Dialog or in the operation menu of the Searching Filter (in case of usage the option searchOperators: true of the method filterToolbar). Like "equal" text.

Let us now that we have local grid with the items of data like

[
    ....
    { id: "21",  invdate: "2007-10-02", name: "test2",  amount: 351.75, tax: 23.45, ... },
    { id: "41",  invdate: "2007-10-04", name: "test4",  amount: 200, tax: 10, ... },
    ....
] 

and we want to define new operation which will allows to specify multiple alternative value for searching/filtering. For example the user can specify 10;20;30 in the tax column to search for any tax values from the list 10, 20, 30. It corresponds tax IN (10, 20 , 30) operation of SQL.

The solution of the problem implemented in free jqGrid 4.8

To implement the custom searching in free jqGrid 4.8 we need to do the following:

1. We need to choose the set names of new operation which jqGrid should use. Let us we have chosen the names "nIn" "nIN" and "numeric IN", for the new "numeric IN" operation, which corresponds the names "eq", "==" and "equal" of the standard built-in eq operation.

2. We need to define customSortOperations parameter of jqGrid

customSortOperations: {
    nIn: {
        operand: "nIN",
        text: "numeric IN"
    }
}

3. one should add nIn operation in the list of operations supported by the column tax:

{ name: "tax", ..., searchoptions: { sopt: ["nIn", "eq", "ne", ...] } }

4. To implement the searching/filtering locally jqGrid need to know how to implement the filtering by new "nIn" operation. One have to add the filter callback:

customSortOperations: {
    nIn: {
        operand: "nIN",
        text: "numeric IN",
        filter: function (options) {
            // The method will be called in case of filtering on the custom operation "nIn"
            // All parameters of the callback are properties of the only options parameter.
            // It has the following properties:
            //     item        - the item of data (exacly like in mydata array)
            //     cmName      - the name of the field by which need be filtered
            //     searchValue - the filtered value typed in the input field of
            //                   the searching toolbar

            // the callback should return true if the item of data corresponds
            // the searchValue
        }
    }
}

For example if the user types 10;20;30 in the searching toolbar over the column tax then filter callback will be called by jqGrid with every item of the local data. The item property of options will be initialized to the value of the data (for example { name: "test2", tax: 23.45, ... }), the cmName property will be initialized by the name of the column "tax" and the searchValue property will be initialized by the value "10;20;30". The implementation of the filter could be the following for example

filter: function (options) {
    var fieldData = parseFloat(options.item[options.cmName]),
        data = $.map(
            options.searchValue.split(";"),
            function (val) {
                return parseFloat(val);
            }
        );
    return $.inArray(fieldData, data) >= 0;
}

The local variable fieldData will be initialized to the value 23.45 which was read from the tax field of the input item (options.cmName="tax", options.item={..., tax: 23.45, ...}). The expression options.searchValue.split(";") converts the searching value "10;20;30" to array of string items: ["10", "20", "30"] and by using jQuery.map we convert every from the value to number JavaScript type. As the result the local variable data will be initialized to [10, 20, 30]. Finally we use jQuery.inArray to verify whether the value 23.45 (the value of tax field of the item) is in the list of the values asked by the user [10, 20, 30]. The callback myCustomIn returns false in the case.

The demo demonstrates the usage of custom operation in the searching toolbar. Another demo demonstrates the usage of custom operation in the searching dialog.

The searching dialog allows to use showQuery: true opetion to display the "formula" of the filtering request. The callback buildQueryValue allows to control the displayed text. The resulting value of customSortOperations will be the following:

customSortOperations: {
    nIn: {
        operand: "nIN",
        text: "numeric IN",
        buildQueryValue: function (otions) {
            // the optional callback can be called if showQuery:true option
            // of the searching is enabled
            return otions.cmName + " " + otions.operand + " (" + 
                otions.searchValue.split(";").join("; ") + ") ";
        },
        filter: function (options) {
            // The method will be called in case of filtering on the custom operation "nIn"
            // All parameters of the callback are properties of the only options parameter.
            // It has the following properties:
            //     item        - the item of data (exacly like in mydata array)
            //     cmName      - the name of the field by which need be filtered
            //     searchValue - the filtered value typed in the input field of
            //                   the searching toolbar

            var fieldData = parseFloat(options.item[options.cmName]),
                data = $.map(
                    options.searchValue.split(";"),
                    function (val) {
                        return parseFloat(val);
                    }
                );
            return $.inArray(fieldData, data) >= 0;
        }
    }
}