Calculating Aggregates in grouped rows

adambelshaw

I've created a table with an aggregate and also with grouped rows. I've tried a few different ways to add an aggregate to group rows, but none worked. I was wondering what the best way to use the api to do this is.

A link to the debug page is here: http://debug.datatables.net/ikomex

The grouping code is in my drawCallback function:

drawCallback: function (settings) {
            var api = this.api();
            var depth = 1;
            var rows = api.rows({ page: 'current' }).nodes();

            //initialise last group names array
            var lastNames = [];
            if(api.data().length > 0)
                depth = api.data()[0].AssetIdPath.match(/\./g).length;
            for (var i = 0; i < depth; i++) {
                lastNames.push("");
            }
            
            api.column(1, { page: 'current' }).data().each(function (group, i) {
                var parentGroups = group.split("/");
                for (var g = 0; g < depth; g++) {
                    if (lastNames[g] !== parentGroups[g]) {
                        $(rows).eq(i).before(
                        '<tr class="group"><td colspan="7">' + parentGroups[g] + '</td></tr>'
                        );

                        lastNames[g] = parentGroups[g];
                    }
                }

            });
        }

This shows multi level groups based on the string in column 1, split by a forward slash. For example 1A / 1A.1 would show a parent group row for 1A, one underneath it for 1A.1, and any 1A.1 rows beneath that.

What I'd like to do is add a cost in here

 '<tr class="group"><td colspan="7">' + parentGroups[g] + '</td></tr>'

I'm guessing I need to marry up the each loop with something like this (this is my totalling for the footer)

             api
                .column(6)
                .data()
                .reduce(function (a, b) {
                    return parseInt(a) + parseInt(b);
                }, 0);

Except not using .column() and maybe using .filter()

Anyone have any ideas? I've tried a few things, but don't think I grok the api fully yet. Sorry I can't get a live demo up.

adambelshaw

Think I worked out how to do it, probably not the most efficient, and I welcome any changes people suggest. I used rows().every()

Here is the complete code for my drawCallback

drawCallback: function (settings) {
            var api = this.api();
            var depth = 1;
            var rows = api.rows().nodes();

            //initialise last group names array
            var lastNames = [];
            if(api.data().length > 0)
                depth = api.data()[0].AssetIdPath.match(/\./g).length;
            for (var i = 0; i < depth; i++) {
                lastNames.push("");
            }
            
            api.table().rows().every(function (rowIdx, tableLoop, rowLoop) {
                var data = this.data();
                var parentGroups = data.AssetName.split("/");
                for (var g = 0; g < depth; g++) {
                    if (lastNames[g] !== parentGroups[g]) {

                        var cost = api
                            .data()
                            .filter(function (filterValue) {
                                var filteredGroups = filterValue.AssetName.split("/");
                                return filteredGroups[g] == parentGroups[g];
                            })
                            .reduce(function (acc, val) {
                                return acc + val.Cost;
                            }, 0);

                        $(rows).eq(rowLoop).before(
                        '<tr class="group"><td colspan="6">' + parentGroups[g] + '</td><td>'+cost+'</td></tr>'
                        );

                        lastNames[g] = parentGroups[g];
                    }
                }

            });
        }

adambelshaw

One thing I've noticed with this approach though, is grouping rows aren't added after searching. Anyone have any ideas on how to fix this?

adambelshaw

The way I've done this is by only looping over displayed rows. I couldn't find a way to do this using the api, so I did it with the internal aiDisplay object in settings. This is an array of numbers that corresponds to the rowidx of the displayed rows.

api.table().rows(api.settings()[0].aiDisplay).every(function (rowIdx, tableLoop, rowLoop) {

allan

The way I've done this is by only looping over displayed rows. I couldn't find a way to do this using the api

table.rows( { page: 'current' } ). See the selector-options documentation for the full list of options. Best to avoid the private API if possible :-) Good to hear you have it working now though!

Allan