jqGrid: using multiple methods to filter data

Question

My requirement is to show a page with multiple filters to apply to grid data.

Suppose we are talking about Orders and an order has the following attributes

public class Order {
    public int OrderID
    public DateTime OrderDate
    public DateTime ShipmentDate
    public int OrderTotal
    public int OrderStatus
}

Inside the jqgrid object I am showing all the attributes except the OrderStatus

The requirement is to create a view that has

• the jqGrid on the left part
• a panel on the right

Inside the right panel the user will see a list of checkboxes that represents every possible OrderStatus value and he want to search using both methods (for example selecting the checkbox "Shipped Orders" and then filtering the grid with Amount greater than a value)

I have already configured the advanced filtering (multiplesearch:true) inside the jqGrid object and I am able to create complex filters combining fields and logical operators.

Any ideas on how I can submit even the data from the right panel when the user press the search button?

Update 1:

Preamble: Oleg sample is fantastic but unfortunately does not fits requirements of my customer :(

@Oleg: I do not understand why you think that:

If the data are outside of the grid you will be show the order details on the right pane only for selected row. So the user will have not so good overview of the data.

maybe my description was not so clear but I am not going to show any order detail. To better clarify my requirement I have modified your sample to show you the desired final UI which is as in the following image:

The customer want to filter the data in the grid using two methods or both together:

• Using the multiplesearch facilities provided by the grid itself (thanks for the workaround mention)
• Using the custom Search Panel (the one with the checkboxes on the right) provided

From a functional point of view the requirement is very easy to express: When the user click on a checkbox or make a search using the native multiplesearch I should post values to the server including also the checkboxes state.

To summarize I should:

• Add the checkboxes state when a post is made through the native multiplesearch
• Add the current multiplesearch state (if any) when the user click on a checkbox

Is there a way to do this?

Answer 1

I understand this requirement very good. In the close case I used checkboxes inside of jqGrid. The most advantage to having the information inside of jqGrid is not only the possibility of easy searching. If the data are outside of the grid you will be show the order details on the right pane only for selected row. So the user will have not so good overview of the data.

To be able to place many checkboxes in the table without permanent horizontal scrolling I rotated headers of the columns having "checkbox with the technique described in http://stackoverflow.com/questions/2705174/vertical-textes-inside-of-table-headers-with-respect-of-a-javascript-based-on-svg. This rotation looks not perfect in IE, but in other browser it works perfect.

You can hold the data from the OrderStatus field in a hidden column and decode the bitmask to boolean which build checkboxes either on the client or on the server side.

Because use want to use multiplesearch:true I have to mention about a bug in jQuery.clone which follow to the bug in jqGrid multi-search in all versions of IE browsers. If you define more as one search filters only the first one will be used because the operation field of all other filters will be read as undefined. It's a pity, but the bug is also not fixed in the jQuery 1.4.3 just published. To be able to use multiplesearch:true you can use workaround suggestion by Jiho Han on trirand.com forum.

All together you can see in the demo example which produce the grid

where you can search for multiple fields

The corresponding code:

var myData = [
    { orderID: "10", orderDate: "2010-09-18", shipmentDate: "2010-09-20", orderStatus: "2" },
    { orderID: "15", orderDate: "2010-09-20", shipmentDate: "2010-09-24", orderStatus: "3" },
    { orderID: "20", orderDate: "2010-10-16", shipmentDate: "2010-10-17", orderStatus: "1" }
];
// decode 'orderStatus' column and add additional boolean data based on the bitmap mask
for (var i=0, l=myData.length; i<l; i++) {
    var myRow = myData[i];
    var orderStatus = parseInt(myRow.orderStatus, 10);
    myRow.airPost = (orderStatus & 2) != 0? "1": "0";
    myRow.heavy = (orderStatus & 1) != 0? "1": "0";
}
var grid = jQuery('#list');
grid.jqGrid({
    data: myData,
    datatype: 'local',
    caption: 'Order Details',
    height: 'auto',
    gridview: true,
    rownumbers: true,
    viewrecords: true,
    pager: '#pager',
    rownumbers: true,
    colNames: ['Order ID', 'Order', 'Shipment', 'Air-Post', 'Heavy', 'RowVersion'],
    colModel: [
        { name: 'orderID', index: 'orderID', key:true, width: 120, sorttype: 'int' },
        { name: 'orderDate', index: 'orderDate', width: 180,
          sorttype: 'date', formatter: 'date' },
        { name: 'shipmentDate', index: 'shipmentDate', width: 180,
          sorttype: 'date', formatter: 'date' },
        { name: 'airPost', width: 21, index: 'airPost', formatter: 'checkbox', align: 'center',
          editoptions: { value: "1:0" }, stype: 'select', searchoptions: { value: "1:Yes;0:No" } },
        { name: 'heavy', width: 21, index: 'heavy', formatter: 'checkbox', align: 'center',
          editoptions: { value: "1:0" }, stype: "select", searchoptions: { value: "1:Yes;0:No" } },
        { name: 'orderStatus', index: 'orderStatus', width: 50, hidden: true }
    ]
}).jqGrid ('navGrid', '#pager', { edit: false, add: false, del: false, refresh: true, view: false },
            {},{},{},{multipleSearch:true})
  .jqGrid ('navButtonAdd', '#pager', { caption: "", buttonicon: "ui-icon-calculator", title: "choose columns",
      onClickButton: function() {
          grid.jqGrid('columnChooser');
      }
  });

where rotateCheckboxColumnHeaders and the bugfix in the advanced search defined so

// we use workaround from http://www.trirand.com/blog/?page_id=393/bugs/in-multiple-search-second-and-subsequent-ops-are-sent-as-undefined-in-ie6/
// to fix the bug in the jQuery.clone (see http://bugs.jquery.com/ticket/6793 and
// dscussion on the http://api.jquery.com/clone/
jQuery.event.special.click = {
    setup: function() {
        if (jQuery(this).hasClass("ui-search")) {
            jQuery(this).bind("click", jQuery.event.special.click.handler);
        }
        return false;
    },
    teardown: function() {
        jQuery(this).unbind("click", jQuery.event.special.click.handler);
        return false;
    },
    handler: function(event) {
        jQuery(".ui-searchFilter td.ops select").attr("name", "op");
    }
};
var rotateCheckboxColumnHeaders = function (grid, headerHeight) {
    // we use grid as context (if one have more as one table on tnhe page)
    var trHead = jQuery("thead:first tr", grid.hdiv);
    var cm = grid.getGridParam("colModel");
    jQuery("thead:first tr th").height(headerHeight);
    headerHeight = jQuery("thead:first tr th").height();

    for (var iCol = 0; iCol < cm.length; iCol++) {
        var cmi = cm[iCol];
        if (cmi.formatter === 'checkbox') {
            // we must set width of column header div BEFOR adding class "rotate" to
            // prevent text cutting based on the current column width
            var headDiv = jQuery("th:eq(" + iCol + ") div", trHead);
            headDiv.width(headerHeight).addClass("rotate");
            if (!jQuery.browser.msie) {
                if (jQuery.browser.mozilla) {
                    headDiv.css("left", (cmi.width - headerHeight) / 2 + 3).css("bottom", 7);
                }
                else {
                    headDiv.css("left", (cmi.width - headerHeight) / 2);
                }
            }
            else {
                var ieVer = jQuery.browser.version.substr(0, 3);
                // Internet Explorer
                if (ieVer !== "6.0" && ieVer !== "7.0") {
                    jQuery("span", headDiv).css("left", 0);
                    headDiv.css("left", cmi.width / 2 - 4).css("bottom", headerHeight / 2);
                }
                else {
                    headDiv.css("left", 3);
                }
                headDiv.parent().css("zoom",1);
            }
        }
    }
};

If you do prefer to hold the checkboxes outside of the grid you can do the decoding of the bit-mask OrderStatus inside of onSelectRow event handler.

UPDATED: I really something misunderstood your requirements at the beginning. Look at the modified example. Now it looks like

and it is more close to what you need.

Answer 2

Just as a follow up I am including here another method that I have found to abtain the same result.

This method suppose to use the postData parameter of jqGrid. Inside the method I have defined various function that verify the checkboxes current status and sends a parameter to the server where it can be used for filtering.

This is a sample

postData: {
    pending: function () {
        if ($("#cb_pending").is(':checked')) {
            return true;
        } else {
            return false;
        }
    }
}

The advantage of this solution respect to the one depicted by Oleg is that it is possible to use mixed logical operators (AND/OR) on the server side whereas using the filters section as in the Oleg answer it is not possible.

Happy coding!