OK, I know I said I was done, but I kept playing around and figured I should share. jqGrid, like other (read: Ext JS) good grid implementations, has the ability to group your data. What does that mean exactly? Well, let's look at our demo for inspiration. We've created a grid of blog post entries. Entries have associated Categories. We can reconfigure our grid to show the entries grouped by Category, with Category headers and accordion like separation.

Accomplishing this is fairly easy, taking a combination of jqGrid configuration, and changes to our data retrieval process. First we'll look at our configuration. We need two new config attributes: grouping and groupingView.

jqGridDemo.js - Grid Configuration

view plain print about
1grid.jqGrid({
2    ...
3    sortname: 'Posted',
4    sortorder: 'asc',
5    postData:{method:"GetGroupedEntries",returnFormat:"JSON"},
6    grouping: true,
7    groupingView: {
8        groupField: ['categoryName'],
9        groupDataSorted: true
10    },
11    ...
12});

Simple enough. We said "Yes, we want a grouping grid", and then told it which column to group on. "Why is the groupField an array?" Good question. The writers of jqGrid are planning to support multiple grouping levels in the future, and will use this array form to define multiple columns at that time. Since jqGrid can only take one column right now, just place that top level grouping column. We also set groupDataSorted to true, so that it will pass the groupField column name as part of the sortCol argument on server-side requests. This means that all server-side requests will now have a sortCol in the form of {groupField} asc, {sortCol}. The need for this will become apparent when we adjust our paging query. Let's look at that next. You'll notice I adjusted the postData attribute, and changed the name of the method we'll call. I also changed our initial sorting column and direction.

In our Entries.cfc, let's copy our GetEntries method, paste it again, and rename the new one to GetGroupedEntries. From there, let's start off by looking at our query. We've never pulled the Categories before, so now we need to adjust to pull in the necessary information. Because there can be multiple categories assigned to an entry, BlogCFC uses a mapping table, tblblogentriescategories, to link entries (tblblogentries) to categories (tblblogcategories). By adding some JOIN statements, and table aliases, we can now add our column to the query.

Entries.cfc - GetGroupedEntries Query

view plain print about
1LOCAL.sql = "SELECT    SQL_CALC_FOUND_ROWS b.id,
2                b.title,
3                c.categoryname,
4                b.posted,
5                b.views
6            FROM    tblblogentries b
7            INNER JOIN tblblogentriescategories bec ON bec.entryidfk = b.id
8            INNER JOIN tblblogcategories c ON c.categoryid = bec.categoryidfk
9            WHERE 0 = 0
10             ";

Then we're required to have our records grouped by category. This is why jqGrid adjusted the sortCol variable, because we need to add the groupField to our ORDER BY clause. You'll recall, though, that we had a bit of code to verify the validity of our sortCol variable. We'll need to adjust this first, or it will throw an error.

Entries.cfc - GetGroupedEntries: Set ORDER BY

view plain print about
1LOCAL.scArr = ListToArray(ARGUMENTS.sortCol);
2LOCAL.sortCol = (ArrayLen(LOCAL.scArr) eq 2) ? LOCAL.scArr[2] : ARGUMENTS.sortCol;
3// Verify that your sort column and direction are valid. If not, then return an error.
4if(ArrayFindNoCase(VARIABLES._COLUMNARRAY, Trim(LOCAL.sortCol)) AND ArrayFindNoCase(VARIABLES._DIRARRAY, ARGUMENTS.sortDir)){
5    LOCAL.orderby = ARGUMENTS.sortCol & " " & ARGUMENTS.sortDir;
6} else {
7    StructAppend(LOCAL.retVal,{"success" = false, "message" = "Your sort criteria is not valid."},true);
8    return LOCAL.retVal;
9}

What are we doin' here? Well, we convert sortCol argument to an array, and create a LOCAL.sortCol variable. If our array has two items, we set our local variable to the second item. Otherwise we just use the argument. We then test the local variable for validity (is this a valid sort column?), and set our LOCAL.orderby. Now we only need two minor adjustments to our javascript, to accomodate the new column. The first is in the colModel in the grid config.

jqGridDemo.js - Grid Configuration: Column Model

view plain print about
1grid.jqGrid({
2    ...
3    colModel: [
4        {name: 'Action', index: 'ID', label: 'Action', width: 60, fixed: true, sortable: false, resizable: false, align: 'center', formatter: 'actionFormatter', key: true},
5        {name: 'Title'},
6        {name: 'Posted', label: 'Release Date'},
7        {name: 'Views', align: 'right', width: 60, fixed: true},
8        {name: 'categoryName'}
9    ],
10    ...
11});

The last bit of script is to add the column in our bits that map the ColdFusion return to the colModel.

jqGridDemo.js - populateGrid: Column Mapping

view plain print about
1grid.jqGrid('setGridParam',{remapColumns:[
2    gridCols['ID'] + gridMultiSelect,
3    gridCols['TITLE'] + gridMultiSelect,
4    gridCols['POSTED'] + gridMultiSelect,
5    gridCols['VIEWS'] + gridMultiSelect,
6    gridCols['CATEGORYNAME'] + gridMultiSelect
7]});
8grid[0].addJSONData(d);

Now we can load our template in the browser to see the result.

Great! We now have grouped data. We can page through, and see where new categories start. You'll notice that our total record count is much higher than before. That's because there's a one-to-many relationship between entries to categories, so entries are repeated in each category that they are a part of. This is great, but the first thing I notice is that the new column actually displays in the grid. Since the category is already the grouping header, I don't think we need to show it in the grid columns. Luckily there's an option to the groupingView that will allow us to hide the column, groupColumnShow.

jqGridDemo.js - Grid Config: groupingView

view plain print about
1grid.jqGrid({
2    ...
3    grouping: true,
4    groupingView: {
5        groupField: ['categoryName'],
6        groupDataSorted: true,
7        groupColumnShow: false
8    },
9    ...
10});

This hid the column but, as you can see, it also changed the size of the grid when it removed the column. This is a bug in jqGrid, and is filed with the development team, but in the meantime you can add some code to handle this issue. Our gridComplete configuration attribute is already setup to call our gridLoadInit method, which is called every time the data loads. We'll add a line at the end of this method to repair our grid width.

jqGridDemo.js - gridLoadInit Method

view plain print about
1var gridLoadInit = function () {
2    ...
3    grid.jqGrid('setGridWidth',800);
4};

If we reload our page again, we'll see that the grid width is now as it should be, with the column being hidden as intended. What's next?

Each grouping section header has an icon, showing the user that the sections can be collapsed or expanded. In our current configuration (the default) all sections are expanded by default. Let's change that so that they're all collapsed on load. This is easily accomplished by adding groupCollapse to our groupingView configuration. We'll also change the classes of the expand and collapse indicators used, so that they match up with the rest of our demo. We're using the FamFamFam Silk icon library, so we'll reference some images from that.

jqGridDemo.js - groupingView Configuration

view plain print about
1groupingView: {
2    groupField: ['categoryName'],
3    groupDataSorted: true,
4    groupColumnShow: false,
5    groupCollapse: true,
6    plusicon: 'bullet_toggle_plus',
7    minusicon: 'bullet_toggle_minus'
8},

The plusicon and minusicon attributes denote class references. We'll create these in our stylesheet.

jqGridDemo.css - icon references

view plain print about
1/* bullet_toggle_minus icon image for trigger */
2.bullet_toggle_minus { background: url('/resources/images/icons/bullet_toggle_minus.png') no-repeat scroll 0px 0px transparent !important; }
3
4/* bullet_toggle_plus icon image for trigger */
5.bullet_toggle_plus { background: url('/resources/images/icons/bullet_toggle_plus.png') no-repeat scroll 0px 0px transparent !important; }
6
7.ui-jqgrid tr.jqgroup td { font-size: 1.1em; font-weight: bold !important; background-color: #98AFC7; }

When you reload your page you'll see that the groupings are now collapsed by default, and notice the new icons. I even added some style stuff to differentiate the headers from the data a little more clearly. Now, personally, I would prefer that all of the sections are collapsed on load, except the first one. We can do this by adding a line to our gridLoadInit method.

jqGridDemo.js - gridLoadInit Method

view plain print about
1var gridLoadInit = function () {
2    ...
3    grid.jqGrid('groupingToggle','gridTestghead_0');
4    grid.jqGrid('setGridWidth',800);
5};

The groupingToggle method is used to open or close a grouping session, taking the row id as the argument. Grouping headers are just another table row, each with it's own unique id. It's format is {grid id}ghead_{section index}. Our grid id is gridTest, and JavaScript uses 0 based indexes, so the first section is index 0. Placing this in the gridLoadInit, a request for data is made, the data is applied to the grid in groups, the groups are collapsed (and the column is hidden) by configuration, the load method is called, then the first section is expanded, and the width is set on the grid. Whew! That's a lot of stuff. It now looks like this.

This is coming along very nicely. There's just one final thing I'd like to explore here, summary rows. One common function of grouping is to have summaries of specific columns: calculations (or functions) run on each record of data in a group to arrive at some 'summary' value. For instance, you might have some sort of user logging setup, and want to show the average number of page views per user. Or an e-commerce control panel, where you are grouping user orders, and want to show the total amount that's been spent by clients across all of their grouped orders. In our demo we show how many times each post has been viewed, so we'll break it down to the total number of views per category. We begin by adjusting our column model, to say we want to tally up the views.

jqGridDemo.js - jqGrid Config: Column Model

view plain print about
1grid.jqGrid({
2    ...
3    colModel: [
4        ...
5        {name: 'Views', align: 'right', width: 60, fixed: true, summaryType: 'sum'},
6        ...
7    ],
8    ...
9});

There are several different summaryTypes that may be applied to a column.

  • sum - apply the sum function to the current group value and return the result
  • count - apply the count function to the current group value and return the result
  • avg - apply the average function to the current group value and return the result
  • min - apply the min function to the current group value and return the result
  • max - apply the max function to the current group value and return the result

You can also apply a custom function, or even a template, to a column. For more information see the jqGrid wiki entry on Grouping. I don't have enough space in the Views column to put a label on that total, so I need something in the Release Date footer. Since I'm not really creating a true summary of that column I need to apply an empty method to the summaryType, then apply my output to the summaryTpl like this:

jqGridDemo.js - jqGrid Config: Column Model

view plain print about
1var emptyMethod = function (){
2    return;
3};
4
5grid.jqGrid({
6    ...
7    colModel: [
8        ...
9        {name: 'Posted', label: 'Release Date', summaryTpl: '<div class=\"dateSummaryFooter\">Total Views: <\/div>', summaryType: emptyMethod},
10        ...
11    ],
12    ...
13});

After that I adjust my groupingView to give me the summary:

jqGridDemo.js - jqGrid Config: groupingView

view plain print about
1groupingView: {
2    groupField: ['categoryName'],
3    groupDataSorted: true,
4    groupColumnShow: false,
5    groupCollapse: true,
6    plusicon: 'bullet_toggle_plus',
7    minusicon: 'bullet_toggle_minus',
8    groupSummary: [true]
9},

Then I'll add a little styling, to make it stand out, and align my summaryTpl output.

jqGridDemo.css

view plain print about
1div.dateSummaryFooter { text-align: right; }
2
3tr.jqfoot td { background-color: #736F6E; color: #FFF; }

When you refresh the page you'll see how it all lays out.

As you can see, grouping in jqGrid is pretty easy to do. There are more options to refine your grouping view as well. See the documentation for more. As always, I welcome your feedback. Sample code can be obtained from the Download link below.