Home / Questions / 445454
Accepted
John Doe
Asked: 2022-04-20 10:51:41 +0800 CST 2022-04-20 10:51:41 +0800 CST

How to group child rows in Datatables

  • 772

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

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

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

+-----------------------------------------------------------------------+
|    | Orden de compra  | Fecha de Orden de Compra | Moneda |   Estado  |
+----+------------------+--------------------------+--------+-----------+
|  + |        258       |       06/01/2020         |   USD  | Entregado |                                
+------+---------+------------+-------------------+-------------+-------+
|   Fecha de recepción  | No. Factura | Código Articulo |  Descripción  |
+------+---------+-----------+--------------------+-------------+-------+
|       07/01/2020      |     617     |      CA0033     |       CT      |
+-----------------------------------------------------------------------+
|  + |        258       |       06/01/2020         |   USD  | Entregado |
+-----------------------+--------------------------+--------+-----------+
|   Fecha de recepción  | No. Factura | Código Articulo |  Descripción  |
+-----------------------+-------------+-----------------+---------------+
|       14/01/2020      |     620     |      CA0036     |      CTR      |
+-----------------------+-------------+-----------------+---------------+
|  + |        258       |       06/01/2020         |   USD  | Entregado |
+-----------------------+--------------------------+--------+-----------+
|   Fecha de recepción  | No. Factura | Código Articulo |  Descripción  |
+-----------------------+-------------+-----------------+---------------+
|       16/01/2020      |     626     |      CC0048     |      CTY      |
+-----------------------+-------------+-----------------+---------------+

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

    +-----------------------------------------------------------------------+
    |    | Orden de compra  | Fecha de Orden de Compra | Moneda |   Estado  |
    +----+------------------+--------------------------+--------+-----------+
    |  + |        258       |       06/01/2020         |   USD  | Entregado |                                
    +------+---------+------------+-------------------+-------------+-------+
    |   Fecha de recepción  | No. Factura | Código Articulo |  Descripción  |
    +------+---------+-----------+--------------------+-------------+-------+
    |       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 grouped the information of the 3 same orders, this is what I want to get to.

The following is the code for 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>Fecha de Recepción: </strong></td>' + '<td><strong>No. Factura:<strong></td>' +  '<td><strong>Codigo Art:<strong></td>' +  '<td><strong>Descripcion Art:</strong></td>' +
        '</tr>' +
        '<tr>' +
        '<td>' + d.FechaFactura + '</td>' + '<td>' + d.Factura + '</td>' + '<td>' + d.Codigo + '</td>' +  '<td>' + d.Descripcion_Articulo + '</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" : "OrdenCompra" },
             { "data" : "FechaOrdenCompra" },
             { "data" : "TipoMoneda" },
             { "data" : "Estatus" }                 
        ],
         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 secondary rows or child rows. I would be grateful to each of you if you can help me find a way out of this problem.

It is important to remember that, as the main row, I require Purchase Order, Purchase Order Date, Currency and State, and as a secondary row, I need to locate the Receive Date, Invoice No., Item Code and Description.

Update:

I attach a handmade example of what I want to build with my table:

enter image description here

If you look, I have two purchase orders that each contain a sub-row with information. Instead of repeating each of the purchase orders, I group them into one depending on their ID.

In my Javascript it looks like this:

/* Formatting function for row details - modify as you need */
function format(d) {
    // `d` is the original data object for the row
    console.log(d);
        let tabla = `<table cellpadding="5" cellspacing="0" style="border-collapse: separate; border-spacing: 40px 5px;">
                    <thead>
                        <tr>
                            <th>
                                Fecha de recepción
                            </th>
                            <th>
                                No. Factura
                            </th>
                            <th>
                                Codigo Art
                            </th>
                            <th>
                                Descripcion
                            </th>
                        </tr>
                        </thead>
                        <tbody>`;
                            d.Factura.forEach(f => {
                                tabla += `<tr>
                                <td>${f.FechaFactura}</td>
                                <td>${f.Factura}</td>
                                <td>${f.Codigo}</td>
                                <td>${f.Descripcion_Articulo}</td>
                                </tr>`;
                            });
                       tabla += '</tbody></table>';
                       return tabla;
                       }

When I reload my table I get the following error:

enter image description here

UPDATE 4:

Now I get the following in the response to my JSON:

enter image description here

After trying the last modification to the response I now get the following as a response to my JSON.

enter image description here

UPDATE 5:

I am now getting the following as a response to my JSON

enter image description here

And as alertwhen loading the table from the Datatables view I get the following:

enter image description here

UPDATE 6:

After trying the code found in the answer I get a result in the Datatable but all child rows are grouped in a single parent row to avoid this I built the following data to group by parent row and child row.

After whileadd the following grouped data:

// -- Aquí tienes toda tu data agrupada
                $group_data = group_array($query, "OrdenCompraGroup");

                // -- Creamos un nuevo array para la nueva data procesada
                $new_data = array();

                // -- Vamos armar un array padre e hijo
                foreach($group_data as $itemPadre) {
                    // -- Variables que necesitas para el padre
                    $ordenCompra = "";
                    $fechaOrdenCompra = "";
                    $TipoMoneda="";       
                    $Estatus = "";                  
                    
                    // -- Declaramos la variable donde se almacenaran los datos hijos
                    $arrayFactura = array();

                    // --
                    foreach($itemPadre["groupdata"] as $itemHijo) {
                        // -- Aquí llenamos las variables del padre, ya que se encuentran dentro del groupdata
                        $ordenCompra = $itemHijo["OrdenCompra"];
                        $fechaOrdenCompra = $itemHijo["FechaOrdenCompra"];
                        $TipoMoneda = $itemHijo["TipoMoneda"];
                        $Estatus = $itemHijo["Estatus"];

                        // -- Aquí llenamos la data del hijo
                        $arrayFactura[] = array(
                            "Factura" => $itemHijo["Factura"],                          
                            "FechaFactura" => $itemHijo["FechaFactura"], //Fecha de recepcion,
                            "Codigo" => $itemHijo["Codigo"],
                            "Descripcion_Articulo" => utf8_encode ($itemHijo["Descripcion_Articulo"]),

                        );
                    }

                    // -- Aquí llenamos el array general (En este punto tenemos los datos del padre y del hijo)
                    $new_data[] = array(
                        "OrdenCompra" => $ordenCompra,              // -- PADRE
                        "FechaOrdenCompra" => $fechaOrdenCompra,    // -- PADRE
                        "TipoMoneda" => $TipoMoneda, //PADRE
                        "Estatus" => $Estatus,//PADRE
                        "Factura" => $arrayFactura,//HIJO
                    );
                }

But at this point I run into the problem that the data is not displayed in my Datatable, if it's not one thing it's the other.

javascript

4 Answers

  1. Best Answer

    It is necessary to change the data structure to group invoices by purchase order, showing only purchase order in the main table and all related invoices in secondary rows.

    Instead of generating an element for each invoice, it will be done by purchase order, creating a subarray with the corresponding invoices, thus:

    [
    [
        'OrdenCompra' => ###,
        'FechaOrdenCompra' => dd-mm-yyyy,
        'TipoMoneda' => xxx,
        'Estatus' => xxx,
        'Facturas' => [
            [
                'FechaFactura' => dd-mm-yyyy,
                'Factura' => xxx,
                'Codigo' => xxx,
                'Descripcion_Articulo' => xxx
            ],
            // Resto de facturas misma orden de compra
        ]
    ]
],

    Follow these steps:

    1. Add in the query ORDER BY OrdenCompra, Facturato make it easier to create the array.
    2. To avoid any possible errors, define the array before the loop
    3. Define a variable to store the last purchase order
    4. Inside the loop, check if the last command is different from the current row and, if so, insert the row into the array and update the variable
    5. Add the invoice data in the corresponding arrangement
    // Inicializa el arreglo antes del ciclo
$query = [];
// Se necesita número de orden de compra para agrupar
// Por defecto, de la base de datos se obtiene una cadena
$ultimaOrden = '';
while( $row = sqlsrv_fetch_array($stmt) ) {
    // Comparar última orden de compra procesada
    // Forzando la comparación entre cadenas
    if(if(strcmp($ultimaOrden, $row['OrdenCompra']) != 0) {
        // Insertar orden de compra en arreglo
        $query[] = [
           "OrdenCompra" => $row['OrdenCompra'], //Orden de compra
           "FechaOrdenCompra" => $row['FechaOrdenCompra']->format('d/m/Y'),
           "TipoMoneda" => $row['TipoMoneda'], //Moneda
           "Estatus" => $row['Estatus'], //Estatus
           "Facturas" => [] // Arreglo para insertar facturas
        ];
        // Actualizar variable
        $ultimaOrden = $row['OrdenCompra'];
    }

    // Necesitamos acceder siempre al último elemento del arreglo
    $indice = count($query) - 1;
    if($indice < 0) {
        die('Error: No se agregó orden de compra.');
    }

    // Insertar factura actual
    $query[$indice]["Facturas"][] = [
       "FechaFactura" => ($row['FechaFactura'] != null)
           ? $row['FechaFactura']->format('d/m/Y')
           : "", //Fecha de recepcion
       "Factura" => utf8_encode ($row['Factura']), //No Factura
       "Codigo" => utf8_encode ($row['Codigo']), //Codigo Articulo
       "Descripcion_Articulo" => utf8_encode ($row['Descripcion_Articulo'])
    ];
}

// Creas el objeto a devolver
$json = [
    "success" => count($query) > 0 ? true : false,
    "data" => $query
];

echo json_encode($json);

    In Javascript you just need to update the function to create the table with its headers and loop through the invoices to fill. Instead of concatenating the HTML, it's easier and more readable to use text templates :

    function format(d) {
    // Inicializar HTML
    let tabla = `<table cellpadding="5" cellspacing="0" style="border-collapse: separate; border-spacing: 40px 5px;">
         <thead>
         <tr>
            <th>Fecha de Recepción</strong></th>
            <th>No. Factura</th>
            <th>Codigo Art</th>
            <th>Descripcion Art</th>
         </tr>
         </thead>
         <tbody>`;
    // Recorrer facturas para agregar cada fila
    d.Facturas.forEach(f => {
        tabla += `<tr>
            <td>${f.FechaFactura}</td>
            <td>${f.Factura}</td>
            <td>${f.Codigo}</td>
            <td>${f.Descripcion_Articulo}</td>
        </tr>`;
    });
    tabla += '</tbody></table>';
    return tabla;
}

    You should have no problem assembling the data if it is obtained sorted from the query. I'm going to give you an example with Javascript with the same logic used in PHP.

    const data = [
    {
        OrdenCompra: 258,
        FechaOrden: '06/01/2020',
        Moneda: 'USD',
        Estatus: 'Entregado',
        FechaFactura: '07/01/2020',
        Factura: 617,
        Codigo: 'CA0033',
        Descripcion: 'CT'
    },
    {
        OrdenCompra: 258,
        FechaOrden: '06/01/2020',
        Moneda: 'USD',
        Estatus: 'Entregado',
        FechaFactura: '14/01/2020',
        Factura: 620,
        Codigo: 'CA0036',
        Descripcion: 'CTR'
    },
    {
        OrdenCompra: 259,
        FechaOrden: '07/01/2020',
        Moneda: 'USD',
        Estatus: 'Entregado',
        FechaFactura: '16/01/2020',
        Factura: 625,
        Codigo: 'CA0048',
        Descripcion: 'CTY'
    },
    {
        OrdenCompra: 259,
        FechaOrden: '07/01/2020',
        Moneda: 'USD',
        Estatus: 'Entregado',
        FechaFactura: '21/01/2020',
        Factura: 630,
        Codigo: 'CA0033',
        Descripcion: 'CT'
    },
    {
        OrdenCompra: 260,
        FechaOrden: '19/01/2020',
        Moneda: 'USD',
        Estatus: 'Entregado',
        FechaFactura: '25/01/2020',
        Factura: 640,
        Codigo: 'CA0048',
        Descripcion: 'CTY'
    },
];

let ordenes = [];
let ultima = 0;
data.forEach(orden => {
    // Si la última no es igual a la actual, insertar
    if(ultima != orden.OrdenCompra) {
        // Insertar orden y crear arreglo para facturas
        ordenes.push({
            OrdenCompra: orden.OrdenCompra,
            FechaOrden: orden.FechaOrden,
            Moneda: orden.Moneda,
            Estatus: orden.Estatus,
            Factura: []
        });
        // Actualizar última orden
        ultima = orden.OrdenCompra;
    }
    // Obtener índice del último elemento
    let index = ordenes.length - 1;
    // Insertar factura
    ordenes[index].Factura.push({
        FechaFactura: orden.FechaFactura,
        Factura: orden.Factura,
        Codigo: orden.Codigo,
        Descripcion: orden.Descripcion
    });
});

console.log(ordenes);

    Como ves, solo debes asegurarte que los resultados de la consulta tengan el formato y orden adecuado para armar los datos de salida; no es necesario crear otro arreglo para tratar de "componerlo".

    • 8

  2. One way you can achieve this is by using the fnOpenand functions fnCloseof dataTables, without using the RowGroup extension. Not sure if there is an updated version, these features are in the legacy version

    Basically instead of having your child rows added one by one to your main table, you make all the child rows one complete table. Then on your + button you call fnOpenand pass the full child table to it.

    It would be something like this, very similar to what you are doing right now:

    
$(document).ready(function () {
    // La tabla principal se declara aquí y se guarda en la variable
    var dataTable = $('#example').dataTable( {
        responsive : true,
         ajax : {
             "type": 'POST',
             "url" : './test.php',  
             "dataType": 'JSON',             
             "cache": false,
             "data": {
                 'param' : 1,                           
             },
         }
...
    })


   $('#example').on('click', 'td.details-control', function () {
        
        var row = this.parentNode; // Encuentra el nodo 'tr'
     
        if ($(row).next().find('td').hasClass('detalles')) {
           // está abierta, hay que cerrarla
            dataTable.fnClose(row);
        }
        else {
            // La abres, o si necesitas puedes cargar la tabla con ajax
            dataTable.fnOpen(row, tablaSecundaria, 'detalles')
        }
    });

    If you decide to load it with ajax, you would do it in the else block .

    • 2

  3. Mis recomendaciones son las siguientes

    1. Creas un servicio en Backend solo para obtener data de ordenes de compras
    2. Creas otro servicio solo para devolver recibos dada un ID de Orden de compra
    3. La datatable la asocias solo con primer servicio
    4. El segundo Servicio lo llamas desde el Front al dar click en el boton + que extiende la fila
    5. Renderizas la mini-tabla una vez recibes el servicio
    • 0

  4. Puedes pre-serializar la agregación.
    Obteniendo desde la base de datos un json formateado con las facturas:

    SELECT vij.purchId as 'OrdenCompra'
     , pt.createdDatetime as 'FechaOrdenCompra'
     , f.moneda as 'TipoMoneda'
     , pt.purchStatus as 'Estatus'
     , '[{'
       + string_agg( concat( '  "Factura"    :"', f.factura      , '"'
                           , ', "Fecha"      :"', vij.invoiceDate, '"'
                           , ', "Codigo"     :"', vit.itemId     , '"'
                           , ', "Descripcion":"', it.nameAlias   , '"'
                           )
                   , '},{'
                   ) within group (ORDER BY f.factura DESC)
       +'}]' Facturas
  FROM pp_facturas f
    INNER JOIN dirPartyTable e 
      ON e.dataArea = f.dataAreaId    
    LEFT JOIN vendInvoicejour vij 
      ON    vij.dataAreaId = f.dataAreaId 
        AND vij.purchId = f.ordenCompra
        AND vij.invoiceId like '%'+f.factura 
    LEFT JOIN vendInvoiceTrans vit 
      ON    vit.purchId = vij.purchId 
        AND vit.invoiceId = vij.invoiceId 
        AND vit.numberSequenceGroup = vij.numberSequenceGroup 
        AND vit.internalInvoiceId = vij.internalInvoiceId 
        AND vit.dataAreaId = vij.dataAreaId      
    LEFT JOIN inventDim ind 
      ON    ind.inventDimId = vit.inventDimId 
        AND ind.dataAreaId = vit.dataAreaId
    LEFT JOIN inventTable it 
      ON    vit.itemId = it.itemId 
        AND vit.dataAreaId = it.dataAreaId
    LEFT JOIN purchTable pt 
      ON    pt.purchId = f.ordenCompra 
        AND pt.dataAreaId = f.dataAreaId
  WHERE f.rfc = 'abc000000000'
    AND f.baja = 0
    AND e.organizationType = 1
  GROUP BY vij.purchId
         , pt.createdDatetime
         , f.moneda
         , pt.purchStatus

    En PHP de-serializas en un segundo nivel:

    $record = array( "OrdenCompra"       => $row['OrdenCompra'], //Orden de compra
                 "FechaOrdenCompra"  => $row['FechaOrdenCompra']->format('d/m/Y'), //Fecha de orden de compra
                 "TipoMoneda"        => $row['TipoMoneda'], //Moneda
                 "Estatus"           => $row['Estatus'], //Estatus
                 "Facturas"          => json_decode($row['Facturas'])
               );

    Para que el AJAX reciba algo como:

    [ { OrdenCompra: 261
  , FechaOrdenCompra: '22/02/2020' 
  , Facturas : [ { Fecha : '03/03/2020', Factura : 679 }
               ]
  }
, { OrdenCompra: 262
  , FechaOrdenCompra: '03/03/2020' 
  , facturas : [ { Fecha : '07/01/2020', Factura : 617 }
               , { Fecha : '04/01/2020', Factura : 620 }
               , { Fecha : '16/01/2020', Factura : 626 }
               ]
  }
]

    Para formatear las filas secundarias:

    function format(d) {
    var html = '<table cellpadding="5" cellspacing="0" style="border-collapse: separate; border-spacing: 40px 5px;">'
             + '<tr>' 
             + '<td><strong>Fecha de Recepción: </strong></td>' 
             + '<td><strong>No. Factura:<strong></td>' 
             + '</tr>';
    d.Facturas.forEach( (fac) => {
        html += '<tr>' 
              + '<td>' + fac.Fecha + '</td>' 
              + '<td>' + fac.Factura + '</td>' 
              + '</tr>';
    });
    html += '</table>';
       
    return html;
}

    Well you can get the same thing by doing a PHP query for each purchase order, but if you take care that null values ​​don't affect the json format, you can get it in a single trip to the database.

    • 0