Sort Categories by rowGroup.count

worgor

Hi

Is it in some way possible to sort the categories built by rowGroup by the number of .count for each catergory? What i want is to categorize a list of log entries and the categories with the highest numbers should be topmost.

Alternatively is it possible to set the row.count() number in the rowGroup into every detail row of the specific category? This would solve the problem also i think.

Maybe someone has a hint in this issue!

colin

Hi @worgor ,

This thread should help, it's asking the same thing. There's an example low down on the thread that should get you going.

Cheers,

Colin

worgor

Colin you are a star, thx a lot, i'll have look at it right now!! Thx!

worgor

Hi Colin
I tried your approach but was not very successful, unfortunatly i've to leave the ajax injection in my sample code so it's pretty hard to make a fiddle ... maybe you can have a look at it and tell me what i'm doing wrong - the categories are showing correct but the sorting of the total count is not working.

 $(".durationset").on("click", function(){

        currentDat = moment().add(-$(this).data('period'), 'days').format('DD.MM.YYYY');
        $('#fromdate').val(currentDat);
        example.ajax.reload(null, false);

    })




    var collapsedGroups = {};
    var qtys = {};
    var salesGroupSums = {};
    var groupColumn = 0;

    var example =
        $('#errortable').DataTable( {
            "language": {
                "url": "//cdn.datatables.net/plug-ins/1.10.19/i18n/German.json",
                "decimal": ",",
                "thousands": ".",
                "lengthMenu": "Zeige _MENU_ Ergebnisse pro Seite",
                "zeroRecords": "Nix gefunden - sorry",
                "info": "Zeige Seite _PAGE_ von _PAGES_",
                "infoEmpty": "Keine Daten vorhanden",
                "infoFiltered": "(gefiltert aus insg. _MAX_ Ergebnissen)"
            },
            "processing": true,
            pageLength: -1,
            lengthMenu: [[50, 100,  -1], [50,100, "All"]],
            ajax: {
                url: '/errorLog',
                dataSrc: '',
                type: "POST",
                data: function ( d ) {
                    d.fromdate = $('#fromdate').val();
                    d.untildate = $('#untildate').val();
                }
            },
            dom:
                "<'row'<'col-sm-9 text-left'B><'col-sm-3'f>>" +
                "<'row'<'col-sm-12'tr>>" +
                "<'row'<'col-sm-2'l><'col-sm-3'i><'col-sm-7'p>>",
            buttons: [
                { "extend": 'print', "text":'<span class="glyphicon glyphicon-print "><span class="fontbtn"> Print</span></span>',"className": 'btn btn-default btn-sm' },
                { "extend": 'excelHtml5', "text":'<span class="glyphicon glyphicon-file"><span class="fontbtn"> Excel</span> </span>',"className": 'btn btn-default btn-sm'},
                { "extend": 'csvHtml5', "text":'<span class="glyphicon glyphicon-list-alt"><span class="fontbtn"> CSV</span> </span>',"className": 'btn btn-default btn-sm'},
                { "text": '<span  class="glyphicon glyphicon-sort-by-attributes-alt "><span class="fontbtn group-by" style="color:white"> by date</span> </span>',"className": 'btn btn-primary btn-sm',
                    action: function ( e, dt, node, config ) {
                        e.preventDefault();
                        sortcol=3;
                        example.rowGroup().dataSrc("date");
                        example.draw(false);
                    }
                },
                { "text": '<span  class="glyphicon glyphicon-sort-by-attributes-alt "><span class="fontbtn group-by" style="color:white"> by occurence</span> </span>',"className": 'btn btn-primary btn-sm',
                    action: function ( e, dt, node, config ) {
                        e.preventDefault();
                        sortcol=0;
                        example.rowGroup().dataSrc("code");
                        example.draw(false);
                    }
                },
                { "text": '<span  class="glyphicon glyphicon-sort-by-attributes-alt "><span class="fontbtn group-by" style="color:white"> by teststation</span> </span>',"className": 'btn btn-primary btn-sm',
                    action: function ( e, dt, node, config ) {
                        e.preventDefault();
                        sortcol=2;
                        example.rowGroup().dataSrc("testStation");
                        example.draw(false);
                    }
                },
                { "text": '<span  class="glyphicon glyphicon-sort-by-attributes-alt "><span class="fontbtn group-by" style="color:white"> by user</span> </span>',"className": 'btn btn-primary btn-sm',
                    action: function ( e, dt, node, config ) {
                        e.preventDefault();
                        sortcol=1;
                        example.rowGroup().dataSrc("updateuser");
                        example.draw(false);
                    }
                }


            ],
            //here comes my ajax injection, left out for live.datatables example
            //orderFixed: {post:[ 0, 'asc' ]},
            columns: [
                { data: "code" },
                { data: "updateuser" },
                { data: "testStation" },
                { data: "date" },
                {render: function(data, type, row, meta) {
                        return (type !== 'sort' || qtys[row[groupColumn]] === undefined)? 1 : qtys[row[groupColumn]].split(' ')[0];
                    }},
            ],
            rowGroup: {
                endRender: null,
                startRender: function ( rows, group ) {
                    var collapsed = !!collapsedGroups[group];
                    rows.nodes().each(function (r) {
                        r.style.display = collapsed ? 'none' : '';
                    });


                    if (qtys[group] === undefined) {

                        var qty = rows.count()
                        qty = $.fn.dataTable.render.number( '.', ',', 0, '', ' Stk.' ).display( qty );

                        qtys[group] = qty;

                        example.rows().invalidate();

                    }


                    return $('<tr/>')
                        .append( '<td colspan="4">Summe '+group+'</td>' )
                        .append( '<td>'+qtys[group]+'</td>' )
                        .attr('data-name', group)
                        .toggleClass('collapsed', collapsed);
                },
                dataSrc: groupColumn
            },
            initComplete: function() {
                example.rows().invalidate();

                // Start with closed groups
                $('#errortable tbody tr.group-start').each(function() {
                    var name = $(this).data('name');
                    collapsedGroups[name] = !collapsedGroups[name];
                });
                example.draw(false);
            }
        });


    // Change the fixed ordering when the data source is updated
//     example.on( 'rowgroup-datasrc', function ( e, dt, val ) {
//         example.order.fixed( {pre: [[ val, 'asc' ]]} ).draw();
//     } );

    $('a.group-by').on( 'click', function (e) {
        e.preventDefault();

        groupColumn = $(this).data('column');
        example.rowGroup().dataSrc( $(this).data('column') );

        example.rows().invalidate().order(groupColumn).draw();
    } );

    // Collapse Groups
    $('#errortable tbody tr.group-start').each(function() {
        var name = $(this).data('name');
        collapsedGroups[name] = !collapsedGroups[name];
        example.draw(false);
    });


    $('#errortable tbody').on('click', 'tr.group-start', function () {
        var name = $(this).data('name');
        collapsedGroups[name] = !collapsedGroups[name];
        example.draw(false);
    });


    $("#refreshview").on("click", function(){
        example.ajax.reload(null, false).responsive.recalc().columns.adjust().draw();
    });

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

colin

Hi @worgor ,

That's a lot of code to look at. You can create a fiddle with static data, just use data with a small dataset, and that would help to diagnose the issue.

Cheers,

Colin

worgor

hi colin

i tried it here
http://live.datatables.net/hujabuhi/3/edit
but it won't even group the rows ... in general it's the original post with a few modifications - i thought removing the sum() and just placing the count wouldn't be a problem.

kthorngren

Looking at the browser's console you are getting this error:

VM708:39 Uncaught TypeError: Cannot read property 'rows' of undefined

Is there a reason why you are using example.rows().invalidate(); in startRender? You are getting the same errors inside initComplete. Likely you don't want to use invalidfate() or draw() in these two places.

Commented out those lines, here is the updated example:
http://live.datatables.net/hujabuhi/4/edit

I haven't followed the thread closely so not sure what exactly you are trying to show in the test case. But its running now so Colin can take a look or if you want to summarize what the test case should be doing that its not it would be helpful.

Kevin

worgor

oh thx kevin, i missed this line, and no there is no reason for the .invalidate() in startRender() - i was just minimizing the changes of the original sample from this link https://datatables.net/forums/discussion/comment/143344#Comment_143344 - i tried to adopt this sample colin mentioned i failed a couple of times so i just wanted to have a working sample ... i've no clou why it is behaving differnt to the oirignal sample ... Thx again Kevin

worgor

To summarize the testecase - what i want to achieve is that the row.count() is sortable in a seperate column even if it is created in the row.group() start event. so the goal is that all documents are counted under a specific group and can then be sorted so people can see where the most errors occured!

colin

Hi @worgor ,

I think we should move away from that example and look at your specific data, since those links are confusing things.

Does this fit the bill? http://live.datatables.net/januciha/1/edit

I've added an extra column to store the count, which you can then order on.

Cheers,

Colin

worgor

Hi Colin
You are right for sure, there is a lot going on in these samples! The example in the link is somehow only loading but i will try the approach in the row.group(). Thank you very much! Kind regards Andy

worgor

I'm trying out this example but i'm confused - what exactly makes it possible to sort all categories by the "count" column? i removed the "orderfixed" even the "order" ... it had no effect and sometimes it worked, sometimes not ... i think it's just trial and error now because i've no clue what exactly does what ... is there a way to isolate this feature in the code? moreover i've to collapse the categories ... so a lot of complex stuff ... i think i could move to a two table version which would make it much easier ... this would be my favourite though!

worgor

IT WORKS - thank you Colin, thank you Kevin!!! This was there part which made it happen although i don't know what it does :-)

  let length = rows.count();
                rows.every(function(rowIdx, tableLoop, rowLoop) {
                    let data = this.data();
                    data.count = length;
                    bTable.cell(rowIdx, 12).data('xoxxor the mighty');
                })
                return $('<tr/>').append('<td colspan="12">' + group + '</td>').append('<td>' + rows.count() + '</td>')

Here is the complete datatable for everybody who ever wants to collapse all groups and want to sort the row count:

`

let collapsedGroups = {};

let bTable =  $('#errortable').DataTable({
    responsive: true,
    order: [[sortcol, 'desc']],
    pageLength: -1,
    lengthMenu: [[50, 100,  -1], [50,100, "All"]],
    ajax: {
        url: '/errorLog',
        dataSrc: '',
        type: "POST",
        data: function ( d ) {
            d.fromdate = $('#fromdate').val();
            d.untildate = $('#untildate').val();
        }
    },
    dom:
    "<'row'<'col-sm-9 text-left'B><'col-sm-3'f>>" +
    "<'row'<'col-sm-12'tr>>" +
    "<'row'<'col-sm-2'l><'col-sm-3'i><'col-sm-7'p>>",
    processing: true,
    buttons: [
        { "extend": 'print', "text":'<span class="glyphicon glyphicon-print "><span class="fontbtn"> Print</span></span>',"className": 'btn btn-default btn-sm' },
        { "extend": 'excelHtml5', "text":'<span class="glyphicon glyphicon-file"><span class="fontbtn"> Excel</span> </span>',"className": 'btn btn-default btn-sm'},
        { "extend": 'csvHtml5', "text":'<span class="glyphicon glyphicon-list-alt"><span class="fontbtn"> CSV</span> </span>',"className": 'btn btn-default btn-sm'},
        { "text": '<span  class="glyphicon glyphicon-sort-by-attributes-alt "><span class="fontbtn group-by" style="color:white"> by date</span> </span>',"className": 'btn btn-primary btn-sm',
            action: function ( e, dt, node, config ) {
                e.preventDefault();
                sortcol=11;
                bTable.rowGroup().dataSrc("date");
            }
        },
        { "text": '<span  class="glyphicon glyphicon-sort-by-attributes-alt "><span class="fontbtn group-by" style="color:white"> by occurence</span> </span>',"className": 'btn btn-primary btn-sm',
            action: function ( e, dt, node, config ) {
                e.preventDefault();
                sortcol=0;
                bTable.rowGroup().dataSrc("code");
            }
        },
        { "text": '<span  class="glyphicon glyphicon-sort-by-attributes-alt "><span class="fontbtn group-by" style="color:white"> by teststation</span> </span>',"className": 'btn btn-primary btn-sm',
            action: function ( e, dt, node, config ) {
                e.preventDefault();
                sortcol=5;
                bTable.rowGroup().dataSrc("testStation");
            }
        },
        { "text": '<span  class="glyphicon glyphicon-sort-by-attributes-alt "><span class="fontbtn group-by" style="color:white"> by user</span> </span>',"className": 'btn btn-primary btn-sm',
            action: function ( e, dt, node, config ) {
                e.preventDefault();
                sortcol=9;
                bTable.rowGroup().dataSrc("updateuser");
            }
        }

    ],
    rowGroup: {
        endRender: null,
        startRender: function(rows, group) {
           // console.log(group);

            let collapsed = !!collapsedGroups[group];

            rows.nodes().each(function (r) {
                r.style.display = 'none';
                if (collapsed) {
                    r.style.display = '';
                }});

            let length = rows.count();
            rows.every(function(rowIdx, tableLoop, rowLoop) {
                let data = this.data();
                data.count = length;
                bTable.cell(rowIdx, 12).data('xoxxor the mighty');
            })
            return $('<tr/>').append('<td colspan="12">' + group + '</td>').append('<td>' + rows.count() + '</td>')
                .attr('data-name', group)
                .toggleClass('collapsed', collapsed);
        },
        dataSrc: 'date'
    },

    columns: [
        { data: "code" },
        { data: "source" },
        { data: "componentCondition" },
        { data: "component" },
        { data: "condition" },
        { data: "testStation" },
        { data: "error" },
        { data: "testStep" },
        { data: "testCase" },
        { data: "updateuser" },
        { data: "update" },
        { data: "date" },
        {
            render: function(data, type, row, meta) {
                cnt = row.count == undefined || row.count == null ? 0: row.count;
                return  type === 'display'? 1 :cnt;
            }}
    ]
});

$("#refreshview").on("click", function(){
    bTable.ajax.reload(null, false).responsive.recalc().columns.adjust().draw();
});


// Change the fixed ordering when the data source is updated
bTable.on( 'rowgroup-datasrc', function ( e, dt, val ) {
    bTable.order([[ sortcol, 'desc' ]] ).draw();
} );


bTable.on('click', 'tr.dtrg-start', function() {
    let name = $(this).data('name');
    collapsedGroups[name] = !collapsedGroups[name];
    bTable.draw(false);
});`