Custom aggregation/grouping in jqGrid

Question

Overview

I need to somehow customize the jqGrid plugin, either by clever tricks, hacks, or modification of the plugin, to support the following type of aggregation/grouping.

I would be very grateful for some ideas from the jqGrid community on how to implement this.

Details

The aggregation/grouping works as follows.

The user is initially presented with a grid of data as in the following, grouped according to "Column 1." Notice the hyperlinks (e.g. "+5"); these hyperlinks represented "collapsed" rows in which all of the data in those rows have the same values for all columns except the ones with hyperlinks.

By clicking, for example, on the "+5" link, the table expands to display the 5 rows which were previously collapsed into one (replacing the collapsed row that included the "+5" link). Notice that now the expanded rows all have unique values in Column 2, which previously had the "+5" link.

Any ideas on how to implement this? Is this possible in with any other grid plugins?

Answer 1

jqGrid don't support directly such kind of grouping which you need, so one have to implement some kind of manual grouping.

The grouping itself should be done on the server and the server response should contain both extended and collaped rows. I don't quite understand how more as one group expanding link should work so I describe how one can implement the gruping with one link per row. On the other side I decide to implement the collapsing of the previosly expanded row. I do this with respect of additional information included in the JSON data.

Let us the server produce the following JSON data:

{
  "total": 1,
  "page": 1,
  "records": 15,
  "rows" : [
    {"id":"1", "collapseTo":{"id":"6","colName":"column2"}, "expandTo":null, "column1":"534345345", "column2":"3523423423", "column3":"8563564", "column4":"75683", "column5":"4756436", "column6":"87689768", "column7":"8755"},
    {"id":"2", "collapseTo":{"id":"6","colName":"column2"}, "expandTo":null, "column1":"534345345", "column2":"545345343", "column3":"8563564", "column4":"75683", "column5":"4756436", "column6":"7689768", "column7":"8755"},
    {"id":"3", "collapseTo":{"id":"6","colName":"column2"}, "expandTo":null, "column1":"534345345", "column2":"4234235", "column3":"8563564", "column4":"75683", "column5":"4756436", "column6":"7689768", "column7":"8755"},
    {"id":"4", "collapseTo":{"id":"6","colName":"column2"}, "expandTo":null, "column1":"534345345", "column2":"3242323", "column3":"8563564", "column4":"75683", "column5":"4756436", "column6":"7689768", "column7":"8755"},
    {"id":"5", "collapseTo":{"id":"6","colName":"column2"}, "expandTo":null, "column1":"534345345", "column2":"6453334", "column3":"8563564", "column4":"75683", "column5":"4756436", "column6":"7689768", "column7":"8755"},
    {"id":"6", "collapseTo":null, "expandTo":{"column2":["1","2","3","4","5"]}, "column1":"534345345", "column2":"+5", "column3":"8563564", "column4":"75683", "column5":"4756436", "column6":"7689768", "column7":"8755"},
    {"id":"7", "collapseTo":{"id":"9","colName":"column4"}, "expandTo":null, "column1":"763484", "column2":"773845358", "column3":"7657565663", "column4":"87546598", "column5":"75675634", "column6":"89058", "column7":"3453463"},
    {"id":"8", "collapseTo":{"id":"9","colName":"column4"}, "expandTo":null, "column1":"763484", "column2":"773845358", "column3":"7657565663", "column4":"98976989", "column5":"75675634", "column6":"89058", "column7":"3453463"},
    {"id":"9", "collapseTo":null, "expandTo":{"column4":["7","8"]}, "column1":"763484", "column2":"773845358", "column3":"7657565663", "column4":"+2", "column5":"75675634", "column6":"89058", "column7":"3453463"},
    {"id":"10", "collapseTo":{"id":"17","colName":"column6"}, "expandTo":null, "column1":"5378756", "column2":"75675473", "column3":"264523", "column4":"423543", "column5":"34534534", "column6":"909424", "column7":"9768768"},
    {"id":"11", "collapseTo":{"id":"17","colName":"column6"}, "expandTo":null, "column1":"5378756", "column2":"75675473", "column3":"264523", "column4":"423543", "column5":"34534534", "column6":"22934920", "column7":"9768768"},
    {"id":"12", "collapseTo":{"id":"17","colName":"column6"}, "expandTo":null, "column1":"5378756", "column2":"75675473", "column3":"264523", "column4":"423543", "column5":"34534534", "column6":"124376325", "column7":"9768768"},
    {"id":"13", "collapseTo":{"id":"17","colName":"column6"}, "expandTo":null, "column1":"5378756", "column2":"75675473", "column3":"264523", "column4":"423543", "column5":"34534534", "column6":"36727845", "column7":"9768768"},
    {"id":"14", "collapseTo":{"id":"17","colName":"column6"}, "expandTo":null, "column1":"5378756", "column2":"75675473", "column3":"264523", "column4":"423543", "column5":"34534534", "column6":"6856345", "column7":"9768768"},
    {"id":"15", "collapseTo":{"id":"17","colName":"column6"}, "expandTo":null, "column1":"5378756", "column2":"75675473", "column3":"264523", "column4":"423543", "column5":"34534534", "column6":"43536366", "column7":"9768768"},
    {"id":"16", "collapseTo":{"id":"17","colName":"column6"}, "expandTo":null, "column1":"5378756", "column2":"75675473", "column3":"264523", "column4":"423543", "column5":"34534534", "column6":"76543686", "column7":"9768768"},
    {"id":"17", "collapseTo":null, "expandTo":{"column6":["10","11","12","13","14","15","16"]}, "column1":"5378756", "column2":"75675473", "column3":"264523", "column4":"423543", "column5":"34534534", "column6":"+7", "column7":"9768768"}
  ]
}

One can use hidden column to save additional information. I use instead of that jQuery.data which allows to save the information more effectively. The rows with the grouping links contain in the JSON data the information like "expandTo":{"column2":["1","2","3","4","5"]}. Other rows contain "expandTo":null. Inside of loadComplete we can hide all rows having "expandTo":null. In the other rows we will convert the contain of some columns to links. For example if expandTo has {"column2":["1","2","3","4","5"]} then we should convert the item from the column "column2" to the link. As the click action on the link we will hide the row and make all rows from the array ["1","2","3","4","5"] visible.

The demo which demonstrate this you can find here. On clicking of the link the grouping will be expanded. The double click on the expanded rows follows to collapsing the rows and retiring the grid in the previous state.

The corresponding code is:

var grid = $("#list"),
    myExpanding = function(e) {
        var myData = $(this).data("expandTo"), i, l;
        if (myData && typeof(myData.length) !== "undefined" && myData.length>0) {
            for (i=0,l=myData.length; i<l; i++) {
                $("#"+myData[i]).show();
            }
            $(this).closest("tr").hide();
        }
        e.preventDefault();
    }, cmNameToIndex = {};

grid.jqGrid({
    datatype:'json',
    url:'CustomGrouping.json',
    colNames:[//'Expand To',
        'Column 1','Column 2','Column 3','Column 4','Column 5','Column 6','Column 7'],
    colModel:[
        //{name:'expandTo', width:1, hidden:true},
        {name:'column1'},
        {name:'column2'},
        {name:'column3'},
        {name:'column4'},
        {name:'column5'},
        {name:'column6'},
        {name:'column7'}
    ],
    cmTemplate:{width:85,align:'center',sortable:false},
    rowNum:10000,
    gridview:true,
    jsonReader:{repeatitems: false},
    loadComplete: function(data) {
        var i=0, rows = data.rows, l = rows.length, cm = grid[0].p.colModel, rowid,
            idToItem = {}, item;
        // The ids of the rows which has in the first column (expandTo) the information
        // about rows which should be expanded will be saved in the nonHiddenRows array.
        // For the rows the expandingInfo[rowid]
        for(; i<l; i++) {
            item = rows[i];
            idToItem[item.id] = item;
        }
        // fill cmNameToIndex to find easier the column index by column name
        cmNameToIndex = {};
        for (i=0,l=cm.length;i<l;i++){
            cmNameToIndex[cm[i].name] = i;
        }
        $("tr.jqgrow",this).each(function(){
            var tr=$(this), colName, linkInfo, td, rowid = this.id, item = idToItem[rowid];
            //if ($.inArray(rowid,nonHiddenRows) !== -1) {
            if (item && item.expandTo) {
                //tr.show();
                linkInfo = item.expandTo;
                for (colName in linkInfo) {
                    if (linkInfo.hasOwnProperty(colName)) {
                        i = cmNameToIndex[colName];
                        td = $("td:nth-child("+(i+1)+")",this);
                        td.wrapInner($('<a>',
                                       {
                                         href:"#",
                                         click:myExpanding,
                                       }).data("expandTo",linkInfo[colName]));
                    }
                }
            } else {
                tr.hide();
            }
            if (item && item.collapseTo) {
                tr.data("collapseTo",item.collapseTo)
            }
        });
    },
    ondblClickRow: function(rowid, iRow, iCol, e) {
        var tr = $("#"+rowid), tr1 = $(e.target,grid[0].rows).closest("tr.jqgrow"),
            collapse = tr.data("collapseTo"), i, ids, l;
        if (collapse && collapse.id && collapse.colName) {
            i = cmNameToIndex[collapse.colName];
            tr = $("#"+collapse.id);
            ids = $("a", tr[0].cells[i]).data("expandTo");
            if (ids && ids.length) {
                tr.show();
                for(i=0,l=ids.length; i<l; i++) {
                    $("#"+ids[i]).hide();
                }
            }
        }
    },
    height: '100%'
});