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');
        }
    });

});

You can think of a DataTables "child" row as just extra data which was already part of the parent row's source data object. You only have access to that one source object when building a child (hence the comment in your code: "d is the original data object for the row"). You could denormalize your JSON response provided by test.php first (or post-process it using JavaScript), to get access to all the data you want for each child row. Then, d will contain all the data you need. This also solves the "duplicate parent rows" issue at the same time (no duplicate PO rows). — andrewJames
– andrewJames, Commented Apr 19, 2021 at 21:27
@andrewjames You can check my last update add my processed JSON file so that you can access the data that will go in each row — user15500092
– user15500092, Commented Apr 20, 2021 at 16:08
I think there is a misunderstanding about what "denormalized" means here. The sample data in your question does not have one object per unique PO number. It still has many objects per unique PO. So, that is not going to help you. I do not use PHP, but I can show you what I mean using JS, if that helps. — andrewJames
– andrewJames, Commented Apr 20, 2021 at 17:26
@andrewjames I understand, show me what you mean by JS, that would help me a lot — user15500092
– user15500092, Commented Apr 20, 2021 at 17:29

andrewJames · Accepted Answer · 2021-04-23 13:03:25Z

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.)

edited Apr 23, 2021 at 13:03

answered Apr 20, 2021 at 18:20

andrewJames

22.6k11 gold badges32 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

22 Comments

user15500092 Over a year ago

I see that here you use it with a JSON file for this I did it like this but for my real code I use a database query since the data is obtained from there, it is possible to do it with a database query

user15500092 Over a year ago

I put the JSON file as an example since from here I cannot make a direct connection to my database but basically it is the same data

andrewJames Over a year ago

No, I did not use a JSON file. I used an ajax query, same as you.

user15500092 Over a year ago

Apparently your answer did not work for me since it is data obtained from a JSON file in my original code I use a database connection. I see myself in the need to add my PHP code so that all the data can be traversed, you can check in my last update.

andrewJames Over a year ago

Why do you think my data is sourced from a JSON file? (It is not.)

|

Collectives™ on Stack Overflow

How to group child rows in Datatables

1 Answer 1

22 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

22 Comments

Your Answer

Sign up or log in

Post as a guest

Related