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.

enter image description here

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.

enter image description here

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

You describes that the grid rows can be expanded after clicking on the "+5" link. Is it will be later possible to collapse the rows back? I see in your suggestion no interface to do this. — Oleg, May 09 '11 at 20:08
No. Once the rows are expanded, they can't be collapsed again. Thanks. — Donald T, May 09 '11 at 20:45
How do you want to handle intersections? For instance, you could have a group of 5 common values in one column and 5 in another. What do you display if 3 of the rows in the first group are present in the second group? — peteorpeter, May 19 '11 at 13:27
@peteorpeter To the first question: As is in the screenshot, a row can have multiple groupings (i.e. "+3" and "+2" are in a single row). To the second question: The result set is grouped on one particular column, which contains only unique rows for that column; with that constraint, all the other columns are then grouped/collapsed. — Donald T, May 19 '11 at 13:55
@Jack B. So would there not be different grouping patterns depending on the _order_ you process the columns? For instance if you went from left to right, the leftmost column would "purely" group all rows with common values. When you look at the next column you've already collapsed rows. As you go along grouping from left to right, the value set for each column would be smaller and smaller. Is that desirable? Seems like it could be misleading - the count would not reflect commonality across the entire column, but commonality of the values _not already grouped by a previous column_. — peteorpeter, May 19 '11 at 14:16
@peteorpeter & @Jack B. For me the case with two links in one row (like +3 and +2 in the example above) seems espacially suspicious. In the case the orde or grouping can be really important. It is not clear for me how the server can produce the data for such kind of grouping. — Oleg, May 19 '11 at 16:49
On seeing this question, I thought, "That's an interesting table display". I still think that... but it seems to break when the groupings overlap irregularly (rather than _nest_ cleanly). At that point, the data pattern moves from a hierarchical _tree_ to a social _graph_. — peteorpeter, May 19 '11 at 17:24
Thank you for your responses. Let me review them and get back to you. — Donald T, May 20 '11 at 15:05

score 5 · Accepted Answer · answered May 21 '11 at 17:00

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%'
});

Thanks a lot for you answer. I'm reviewing it now, but it looks very, very good, including your demo. — Donald T, May 23 '11 at 21:19
@Jack B.: You are welcome! I choosed the structure `expandTo` in the JSON so that it can hold the information about more as one grouping `column2` and `column4` for example). My answer shows the main idea only how your requirements can be implemented. I hope you can easy modify it to your environment. — Oleg, May 24 '11 at 06:22
We have to make this work for multiple rows, but this answer leads us in the right direction. Thanks! — Donald T, May 24 '11 at 15:44
@Jack B.: I imagined the expanding of multiple links in one column: if you click in the link some previously hidden row will be visible. Some from the new visible rows could have also links. To implement the behavior you should just post from the server the hidden row like in my example. The only difference will be that at the beginning some rows having links inside will be initially hidden. The only problem will be that in case of having for example two links in the row the order of extended the rows is free. So it can be needed to include more (or much more) hidden rows. — Oleg, May 24 '11 at 16:02
@Jack B.: You can also consider to send the data from the server in more compact form and use `addRowData` to add the rows an expanding (in in `click` event handler). In the case you should include the values of **expanding** column additionally to the ids currently used in `expandTo`. In the way you can essentially reduce the size of the server response. — Oleg, May 25 '11 at 08:21
@Oleg: Please look at [this question](http://stackoverflow.com/questions/6939096/hide-grouping-heading-in-jqgrid-if-every-row-inside-it-is-hidden) at your free time — AabinGunz, Aug 06 '11 at 05:59

Custom aggregation/grouping in jqGrid

1 Answers1

Linked