How to group child rows in Datatables

Question

I have a table that makes use of child rows where each row is expandable / collapsible, but my parent rows will have duplicate data.

I would like to group my child rows or child row, as they are called in the official Datatables documentation , I have the following table where in the parent row there are the Purchase Order, Purchase Order Date, Currency and Status columns.

If you look, I have 3 purchase orders that correspond to the same identifier in this example is 258, but each purchase order contains a secondary row with different information, that information is Receipt Date, No. Invoice, Item Code and Description.

+-----------------------------------------------------------------------+
|    | Purchase Order   |    Purchase Order Date   |Currency|   Status  |
+----+------------------+--------------------------+--------+-----------+
|  + |        258       |       06/01/2020         |   USD  | Delivered |                                
+------+---------+------------+--------------------+-------------+------+
|     Receipt Date      | No. Invoice |    Code Item    |  Description  |
+------+---------+-----------+---------------------+-------------+------+
|       07/01/2020      |     617     |      CA0033     |       CT      |
+-----------------------------------------------------------------------+
|  + |        258       |       06/01/2020         |   USD  | Delivered |
+-----------------------+--------------------------+--------+-----------+
|     Receipt Date      | No. Invoice |    Code Item    |  Description  |
+-----------------------+-------------+-----------------+---------------+
|       14/01/2020      |     620     |      CA0036     |      CTR      |
+-----------------------+-------------+-----------------+---------------+
|  + |        258       |       06/01/2020         |   USD  | Delivered |
+-----------------------+--------------------------+--------+-----------+
|      Receipt Date     | No. Invoice |    Code Item    |  Description  |
+-----------------------+-------------+-----------------+---------------+
|       16/01/2020      |     626     |      CC0048     |      CTY      |
+-----------------------+-------------+-----------------+---------------+

What I would like to achieve without repeating the Purchase Order is to group the secondary rows as follows.

+-----------------------------------------------------------------------+
|    | Purchase Order   |    Purchase Order Date   |Currency|   Status  |
+----+------------------+--------------------------+--------+-----------+
|  + |        258       |       06/01/2020         |   USD  | Delivered |                                
+------+---------+------------+-------------------+-------------+-------+
|      Receipt Date     | No. Invoice |    Code Item    |  Description  |
+------+---------+-----------+--------------------+-------------+-------+
|       07/01/2020      |     617     |      CA0033     |       CT      |
+-----------------------+-------------+-----------------+---------------+
|       14/01/2020      |     620     |      CA0036     |      CTR      |
+-----------------------+-------------+-----------------+---------------+
|       16/01/2020      |     626     |      CC0048     |      CTY      |
+-----------------------+-------------+-----------------+---------------+

If you look at the Purchase Order now it contains the information of the same 3 orders grouped together, this is what I want to get to.

The following is the code of my AJAX call which I use to build my tables.

/* Formatting function for row details - modify as you need */
function format(d) {
    // `d` is the original data object for the row
    console.log(d);
    
    return '<table cellpadding="5" cellspacing="0" style="border-collapse: separate; border-spacing: 40px 5px;">' +
        '<tr>' +      
        '<td><strong>Receipt Date: </strong></td>' + '<td><strong>No. Invoice:<strong></td>' +  '<td><strong>Code Item:<strong></td>' +  '<td><strong>Description:</strong></td>' +
        '</tr>' +
        '<tr>' +
        '<td>' + d.ReceiptDate + '</td>' + '<td>' + d.Invoice+ '</td>' + '<td>' + d.CodeItem+ '</td>' +  '<td>' + d.Description + '</td>' +
        '</tr>' +    
        '</table>';     
}


$(document).ready(function () {
    $('#example').dataTable( {
        responsive : true,
         ajax : {
             "type": 'POST',
             "url" : './test.php',  
             "dataType": 'JSON',             
             "cache": false,
             "data": {
                 'param' : 1,                           
             },
         },
         language : {
            "lengthMenu": "Mostrar _MENU_ registros",
            "zeroRecords": "No se encontró nada",
            "info": "Mostrando del _START_ al _END_ de un total de _TOTAL_",
            "infoEmpty": "No hay registros",
            "emptyTable": "No hay datos para mostrar",
            "loadingRecords": "Cargando...",
            "processing": "Procesando...",
            "search": "Buscar:",
            "infoFiltered": "(filtrado de un total de _MAX_ registros)",
            "paginate": {
                "first": "Primera",
                "last": "Última",
                "next": "Siguiente",
                "previous": "Anterior"
            }
         },    
         columns: [          
             {
                 "className":      'details-control',
                 "orderable":      false,
                 "data":           null,
                 "defaultContent": ''
             },
             { "data" : "PurchaseOrder" },
             { "data" : "PurcharOrderDate" },
             { "data" : "Currency" },
             { "data" : "Status" }                 
        ],
         order : [[1, 'desc']]
    } );

    
    // Add event listener for opening and closing details
    $('#example').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = $('#example').DataTable().row(tr);

        if (row.child.isShown()) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child(format(row.data())).show();
            tr.addClass('shown');
        }
    });

});

Consulting the Datatables documentation it has a RowGroup extension but I don't know if they can be grouped for child rows or child rows. I would be grateful to each of you if you can help me find a way out of this problem.

UPDATE:

Below I denormalize my JSON response provided by test.php as requested in the comment, this is done with the goal of getting access to all the data you want for each child row.

[
  {
    "Purchase Order": 949,
    "Purchase Order Date": "20/11/2019",
    "Receipt Date": "12/12/2019",
    "No. Invoice": 448,
    "Code Item": "CC0048",
    "Description": "CTR",
    "Status": "Delivered",
    "Currency": "USD"
  },
  {
    "Purchase Order": 949,
    "Purchase Order Date": "20/11/2019",
    "Receipt Date": "13/12/2019",
    "No. Invoice": 448,
    "Code Item": "CC0048",
    "Description": "CTR",
    "Status": "Delivered",
    "Currency": "USD"
  },
  {
    "Purchase Order": 949,
    "Purchase Order Date": "20/11/2019",
    "Receipt Date": "14/12/2019",
    "No. Invoice": 448,
    "Code Item": "CC0048",
    "Description": "UBC",
    "Status": "Delivered",
    "Currency": "USD"
  },
  {
    "Purchase Order": 949,
    "Purchase Order Date": "20/11/2019",
    "Receipt Date": "15/12/2019",
    "No. Invoice": 448,
    "Code Item": "CC0048",
    "Description": "UBC",
    "Status": "Delivered",
    "Currency": "USD"
  },
  {
    "Purchase Order": 258,
    "Purchase Order Date": "05/12/2019",
    "Receipt Date": "17/12/2019",
    "No. Invoice": 451,
    "Code Item": "CA0033",
    "Description": "CTY",
    "Status": "Delivered",
    "Currency": "USD"
  },
  {
    "Purchase Order": 258,
    "Purchase Order Date": "05/12/2019",
    "Receipt Date": "18/12/2019",
    "No. Invoice": 451,
    "Code Item": "CA0033",
    "Description": "CTY",
    "Status": "Delivered",
    "Currency": "USD"
  },
  {
    "Purchase Order": 258,
    "Purchase Order Date": "05/12/2019",
    "Receipt Date": "19/12/2019",
    "No. Invoice": 452,
    "Code Item": "CA0033",
    "Description": "CTY",
    "Status": "Delivered",
    "Currency": "USD"
  },
  {
    "Purchase Order": 258,
    "Purchase Order Date": "05/12/2019",
    "Receipt Date": "20/12/2019",
    "No. Invoice": 452,
    "Code Item": "CA0033",
    "Description": "CTY",
    "Status": "Delivered",
    "Currency": "USD"
  },
  {
    "Purchase Order": 258,
    "Purchase Order Date": "05/12/2019",
    "Receipt Date": "21/12/2019",
    "No. Invoice": 452,
    "Code Item": "CA0033",
    "Description": "CTY",
    "Status": "Delivered",
    "Currency": "USD"
  }
]

It is important to remember that as parent row I require Purchase Order, Purchase Order Date, Currency and Status and as child row I require to locate Receipt Date, No. Invoice, Code Item and Description.

UPDATE 2:

I add php code to give a little more guidance to my question.

Test.php

<?php
    header('Content-Type: text/html; charset=utf-8');
    
    $param = $_POST['param'];   
    switch($param) {
        case '1': 
                $query = array();
                include './db/conecct.php';
                $sql = "select PURCHID as 'PurchaseOrder',
                CREATEDDATETIME as 'PurchaseOrderDate',
                MONEDA as 'Currency',
                INVOICEDATE as 'ReceiptDate',
                ITEMID  as 'CodeItem',
                FACTURA as 'No. Invoice',
                NAMEALIAS as 'Description',
                PURCHSTATUS as 'Status'         
                FROM PP_FACTURAS
                $stmt = sqlsrv_query($conn, $sql, $params);
                if ( $stmt === false) {
                    die( print_r( sqlsrv_errors(), true) );
                }   
                while( $row = sqlsrv_fetch_array($stmt) ) {
                    //print_r($row);
                    $record = array(
                       "PurchaseOrder"       => $row['PurchaseOrder'],
                       "PurchaseOrderDate"  => $row['PurchaseOrderDate']->format('d/m/Y'),
                       "Currency"        => $row['Currency'],
                       "Status"            => $row['Status'],
                       "PurchaseOrderDate"      => $row['PurchaseOrderDate'] != null ? $row['PurchaseOrderDate']->format('d/m/Y'):"",
                       "No. Invoice"           => utf8_encode ($row['No. Invoice']),
                       "CodeItem"          => utf8_encode ($row['CodeItem']), 
                       "Description"        => utf8_encode ($row['Description']),           
                    );
                    array_push($query, $record);
                }

                sqlsrv_free_stmt( $stmt);       
                sqlsrv_close($conn);

                $json = array(
                    "success"=> count($query) > 0 ? true : false,
                    "data"=>$query
                );

                echo json_encode($json);
            break;

UPDATE 3:

Try to do the answer and now I get the following error in console:

Uncaught TypeError: originalJson is not iterable

I attach the code with which I am supporting myself according to the answer:

/* Formatting function for row details - modify as you need */
function format(d) {
    // `d` is the original data object for the row
    console.log(d);

    var tableHtml = '<table><thead><tr><th>Receipt Date</th><th>Invoice No.</th><th>Item Code</th><th>Description</th></tr></thead>';

       tableHtml = tableHtml + '<tbody>';

        var rowHtml = '';
        for (const rowData of d.details){
            rowHtml = rowHtml + '<tr><td>' + rowData.ReceiptDate + '</td><td>' + rowData.Invoice + '</td><td>' + rowData.CodeItem + '</td><td>' + rowData.Description + '</td></tr>';
        }
        tableHtml = tableHtml + rowHtml + '</tbody></table>';
        return tableHtml;
}


function denormalize(originalJson) {
    let denormalizedMap = new Map();

    for (const element of originalJson) {
        let headerInfo = (({
            PurchaseOrder,
            PurcharOrderDate,
            Currency,
            Status
        }) => ({
            PurchaseOrder,
            PurcharOrderDate,
            Currency,
            Status
        }))(element);
        headerInfo.details = [];

        let detailLine = (({
            ReceiptDate,
            Invoice,
            CodeItem,
            Description

        }) => ({
            ReceiptDate,
            Invoice,
            CodeItem,
            Description
        }))(element);

        if (! denormalizedMap.has(element.PurchaseOrder)) {
            denormalizedMap.set(element.PurchaseOrder, headerInfo);
        }
        denormalizedMap.get(element.PurchaseOrder).details.push(detailLine);
    }

    let denormalizeSource = Array.from(denormalizedMap.values());

    return denormalizeSource;
}

$(document).ready(function () {
    $('#example').dataTable( {
        responsive : true,
         ajax : {
             "type": 'POST',
             "url" : './test.php',  
             "dataType": 'JSON',             
             "cache": false,
             "dataSrc" : function (json){
                 console.log(json);
                 return denormalize(json);
             },
         },    
         columns: [          
             {
                 "className":      'details-control',
                 "orderable":      false,
                 "data":           null,
                 "defaultContent": ''
             },
             { "data" : "PurchaseOrder" },
             { "data" : "PurcharOrderDate" },
             { "data" : "Currency" },
             { "data" : "Status" }                
     
        ],
         order : [[1, 'desc']],
    } );

    
    // Add event listener for opening and closing details
    $('#example').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = $('#example').DataTable().row(tr);

        if (row.child.isShown()) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child(format(row.data())).show();
            tr.addClass('shown');
        }
    });

});

Answer 1

Based on the original question, I assume the JSON provided by your ajax call looks like this:

[
  { "PurchaseOrder": 258,
    "PurcharOrderDate": "06/01/2020",
    "Currency": "USD",
    "Status": "Delivered",
    "ReceiptDate": "07/01/2020",
    "Invoice": 617,
    "CodeItem": "CA0033",
    "Description": "CT"
  },
  { "PurchaseOrder": 258,
    "PurcharOrderDate": "06/01/2020",
    "Currency": "USD",
    "Status": "Delivered",
    "ReceiptDate": "04/01/2020",
    "Invoice": 620,
    "CodeItem": "CA0036",
    "Description": "CTR"
  },
  { "PurchaseOrder": 258,
    "PurcharOrderDate": "06/01/2020",
    "Currency": "USD",
    "Status": "Delivered",
    "ReceiptDate": "16/01/2020",
    "Invoice": 626,
    "CodeItem": "CA0048",
    "Description": "CTY"
  },
  { "PurchaseOrder": 261,
    "PurcharOrderDate": "22/02/2020",
    "Currency": "USD",
    "Status": "Delivered",
    "ReceiptDate": "03/03/2020",
    "Invoice": 679,
    "CodeItem": "CA0062",
    "Description": "CTZ"
  }
];

I added an extra row for PO number 261, just to make the data more varied.

You want to restructure this into a new array, where there is only one object per unique PO number (but where that object also contains multiple items for the related invoice records).

The following data achieves that:

[
  {
    "PurchaseOrder": 258,
    "PurcharOrderDate": "06/01/2020",
    "Currency": "USD",
    "Status": "Delivered",
    "details": [{
      "ReceiptDate": "07/01/2020",
      "Invoice": 617,
      "CodeItem": "CA0033",
      "Description": "CT"
    },
    {
      "ReceiptDate": "04/01/2020",
      "Invoice": 620,
      "CodeItem": "CA0036",
      "Description": "CTR"
    },
    {
      "ReceiptDate": "16/01/2020",
      "Invoice": 626,
      "CodeItem": "CA0048",
      "Description": "CTY"
    }]
  },
  {
    "PurchaseOrder": 261,
    "PurcharOrderDate": "22/02/2020",
    "Currency": "USD",
    "Status": "Delivered",
    "details": [{
      "ReceiptDate": "03/03/2020",
      "Invoice": 679,
      "CodeItem": "CA0062",
      "Description": "CTZ"
    }]
  }
]

Now there are only 2 objects in the array (because there are only 2 unique PO numbers) - but each object contains a details sub-array with the line item details for the related PO number.

Now you have data you can use in your DataTable. The table will display 2 records, and you can build a sub-table from the sub-array for each DataTable "child".

There is a Fiddle showing the full details, including the JavaScript restructuring code, here:

https://jsfiddle.net/ztu2ar0h/

It is missing the URL needed to actually fetch ajax data, so it will not run. (And it is missing the icons for opening and closing child rows.) But everything else is there.

---

Updates

In response to comments made in the answer:

Your PHP code connects to your database, runs a query, and then builds a JSON response, which it sends to DataTables using echo json_encode($json).

You do not actually show us what that JSON looks like. But like I say at the start of my answer, I make an assumption about what it looks like - and I show you that assumed JSON.

This is what your PHP code passes to the DataTable via the DataTable's ajax call.

So, all I am doing in my answer is taking that JSON (sent from PHP to the DataTable) and then re-arranging it into a more useful structure. I am doing this re-arranging inside the DataTable's ajax call:

ajax: {
  method: "GET",
  url: // your URL goes here!,
  dataSrc: function ( json ) { 
    //console.log( json );
    return restructure(json);
  },
},

So, instead of using your raw JSON from your PHP code, I am first re-arranging that raw JSON, using the above JavaScript code, which uses my restructure() function.

This re-arranged JSON is what DataTables uses to build the table.

That's it. It really is as simple as that.

But like I said "I do not use PHP, but I can show you what I mean using JS". And that is what I did, in my Fiddle.

So, if you do not want to use the above approach, you are welcome to take my restructure(json) function and re-write it as PHP code. Then you can do the re-arranging of your raw JSON in your Test.php file yourself - and you can pass the restructured JSON from your PHP file to the DataTable.

It's up to you which approach you want to take. They both do the same thing.

(And there are no JSON files, anywhere, in either of these approaches.)