How to group by multiple columns, jquery datatables

Question

I have a business requirement to format a datatable where results are grouped by 2 columns.

This is what I have for my datatable config which does produce results close to what I need, however I can't get the value of the second column.

The api.column(1 below is referencing the first column, i need column two (2) as well. I tried columns([1, 2] and expected to get an array of results for .each() but that was a failure.

var dtOptions = {
    "dom": '<"clearfix"><"pull-left"l><"pull-right"Bf>r<"horizontal-scroll"t><"pull-left"i><"pull-right"p><"clearfix">',
    "pageLength": 10,
    "paging": true,
    "columnDefs": [
    {
        "targets": groupCols, /// this is an array [1, 2]
        "visible": false
    }],
    "order": [[1, 'asc']],
    "displayLength": 15,
    "drawCallback": function ( settings ) {
        var api = this.api();
        var rows = api.rows( {page:'current'} ).nodes();
        var last = null;

        api.column(1, { page: 'current' }).data().each(function (group, i) {
            if (last !== group) {

                $(rows).eq(i).before(
                    '<tr class="group"><td colspan="3">' + group + '</td><td colspan="14">' + need_second_column_string_here + '</td></tr>'
                );

                last = group;
            }
        });
    }
}

Thanks for any assistance.

Possible duplicate of How to group multiple columns in jquery datatables — Dipiks
– Dipiks, Commented Jan 23, 2017 at 14:18
Perhaps I am just to dense to see it, but I look at that before posting here and don't see how that can help me. — M.G.
– M.G., Commented Jan 23, 2017 at 20:08

M.G. · Accepted Answer · 2017-02-01 20:17:35Z

Thought I would share the solution that worked for me .... see $currTable.rows(rows[i]._DT_RowIndex).data()[0][CommentCol] in the js below

var dtOptions = {
    "dom": '<"clearfix"><"pull-left"l<"toolbar">><"pull-right"Bf>r<"horizontal-scroll"t><"pull-left"i><"pull-right"p><"clearfix">',
    "pageLength": 10,
    "paging": false,
    "columnDefs": [
        { "visible": false, "targets": hiddenCols },
        { "orderable": false, "targets": allColumns }
    ],
    "sort": true,
    "order": [[0, 'asc'], [1, 'asc']],
    "displayLength": 15,
    "drawCallback": function (settings) {
        var api = this.api();
        var rows = api.rows({ page: 'current' }).nodes();
        var last = null;

        var CommentCol = -1;

        _.each(api.columns().header(), function (e, i) {
            if(e.innerHTML == "Comments"){
                CommentCol = i;
            }
        })

        api.column(groupByCol, { page: 'current' }).data().each(function (group, i, $currTable) {
            if (last !== group) {

                $(rows).eq(i).before(
                    '<tr class="group"><td class="nowrap" colspan="3">' + buttons + '<div style="margin-right:6px;"><b>Lot:</b> ' + group + '</div></td><td colspan="16"><b>Comments:</b> ' + $currTable.rows(rows[i]._DT_RowIndex).data()[0][CommentCol] + '</td></tr>'
                );

                last = group;
            }
        });
    }
}

Perhaps not elegant .... but effective.

bh_earth0 · Accepted Answer · 2021-04-30 19:06:52Z

you can hack your way through other libs. will it worth the effort??.

or you can use Tabulator. which has multi column grouping.

example :

  //load data as json
    var tableData = [
        {id:1, name:"Billy Bob", age:"12", gender:"male", height:1, col:"red", dob:"", cheese:1},
        {id:2, name:"Mary May", age:"1", gender:"female", height:2, col:"blue", dob:"14/05/1982", cheese:true},
        {id:3, name:"Christine Lobowski", age:"42", height:0, col:"green", dob:"22/05/1982", cheese:"true"},
        {id:4, name:"Brendon Philips", age:"125", gender:"male", height:1, col:"orange", dob:"01/08/1980"},
        {id:5, name:"Margret Marmajuke", age:"16", gender:"female", height:5, col:"yellow", dob:"31/01/1999"},
        {id:6, name:"Billy Bob", age:"12", gender:"male", height:1, col:"red", dob:"", cheese:1},
    ]
    
    var table = new Tabulator("#example-table", {
        height:"311px",
        layout:"fitColumns",
         groupBy:function(data){ 
            return data.gender + " - " + data.age; //groups by data and age
        },
    autoColumns:true,
    });    

    table.setData(tableData);

<script src="https://unpkg.com/[email protected]/dist/js/tabulator.min.js"></script>
<link href="https://unpkg.com/[email protected]/dist/css/tabulator.min.css" rel="stylesheet"/>


<div id="example-table"></div>

the only table lib that can group by multi col is Tabulator, AFAIK.

here are the other table libs.

                   -------- group by  -------
                   single column | multi column  
tabulator       :        yes     | yes           |  
datatables.net  :        yes     | yes           |  
bootstrap-table :        yes     | no            | 
dynatable.js    :        no      | no            |

tabulator has bootstrap , simple white theme:

theme overview : http://tabulator.info/docs/4.1/theme
live view themes : http://tabulator.info/examples/4.1?#theming

Collectives™ on Stack Overflow

How to group by multiple columns, jquery datatables

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related