Server-side processing DataTable with row details - script

culter

Hi, I have DataTables table with 7 columns and server-side processing and I tried to load another 15 columns and display them in the row details. It's working, but the execution of SQL query takes around 30s. All indexes and other aspects were checked, the query is too complicated

I think the only way is to load the additional data after click on the row. I tried to implement this https://datatables.net/blog/2017-03-31, but there is no sample php script which is used in the ajax request.

Can I use the same script as server_processing.php ? I've tried it, but everytime I click on the row, I get "Loading..."

Thank you for any advice.

allan

Perhaps you can show me the query being executed for your server-side processing? 30 seconds is a huge amount of time to get 10 records (assuming default paging).

Can I use the same script as server_processing.php ? I've tried it, but everytime I click on the row, I get "Loading..."

No it needs to be a different script (or use a condition to serve a different response). Here is the PHP for that example in the blog:

<?php

sleep( 2 );

echo json_encode( [
    "html" => 'Details for <b>'.htmlentities($_POST['name']).'</b><br><br>Full information could be derived from a database and potentially complex interactions such as charts, DataTables or other controls could be shown if required. In this demo only this summary is returned.'
] );

In the real world (like you are doing) you'd want it to connect to the database to get the row details.

Allan

culter

Hi Allan, thank you for the php. It was very helpful. I was able to send the id to another php script, get the data and send back another json. Now I have 1 ajax with url: "scripts/server_processing.php" to load the main table and another ajax with url: "scripts/details.php" to load the details which is executed after click on a row.

Now I'm struggling with displaying the data from details.php in the row details. When I used the previous (slow) solution with just one ajax call to load, I used the function(d), which was great to work with. Now I added the same function, but the data I can show are from the main table, not the details.php. How can I force the function(d) to use data from the second ajax?

My JSON from details.php:

[{
    "TIME": "2018-03-24 11:36:27",
    "DESC": "Created"
}, {
    "TIME": "2015-05-29 21:28:08",
    "DESC": "Solved"
}]

And here is my code:

<script>

function format ( rowData ) {
        console.log(rowData);
        var div = $('<div/>')
                .addClass( 'loading' )
                .text( 'Loading...' );
        $.ajax( {
                url: 'scripts/details.php',
                data: {
                        'data0': rowData[0],
                        'data1': rowData[1],
                },
                dataType: 'json',
                type: 'post',
        } );
// THIS RETURNS DATA FROM MAIN TABLE :(
        return '0: ' + rowData[0] + '<br>' +
                '1: ' + rowData[1] + '<br>' +
                '6: ' + rowData[6] + '<br>' +
                '7: ' + rowData[7] + '<br>' +
                'a: ' + rowData.TIME+ '<br>';
}


$(document).ready(function() {
   var table = $('#tablea').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": {
                "url": "scripts/server_processing.php",
                },
        columns: [
                {
                        "className":    'details-control',
                        "orderable":    false,
                        "data":         null,
                        "defaultContent":       ''
                },
                {data: 0},
                {data: 1},
                {data: 2},
                {data: 3},
                {data: 4},
                {data: 5},
                {data: 6},
        ],
        "lengthMenu": [[10,25,50,100],[10,20,50,100]],
        "order": [[1, 'asc']],
        "columnDefs": [{
                "targets": 7,
                "render": function (data, type, row) {
                        return data.substr( 0,20 );
                        }
        }],

    });


        $('#tablea tbody').on('click', 'td.details-control', function () {
                var tr = $(this).closest('tr');
                var row = table.row( tr );
                var index = table.row( this ).index();
                var data_row = table.row( $(this).parents('tr') ).data();

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


} );
</script>

culter

And here is the sql query:

SELECT table1.ID,
table1.ASKED,
table1.CITY,
table1.TIME,
table1.ITY,
CASE WHEN table1.STATUS=0 THEN "OTVOR" WHEN table1.STATUS=1 THEN "ZATVOR" ELSE "VYKON" END,
table1.REDESC,
table1.BOR,
table1.CAUSE,
group_concat(DISTINCT(table2.TIME)),
group_concat(DISTINCT(table4.POP)),
table1.SPLN,
table3.CHYBAT,
table3.CHYBAS,
table3.CHYBAPOP,
table1.ADDRESS,
table1.PHONE,
table1.CAR
FROM table1
LEFT JOIN table2 ON table1.ID=table2.ID
LEFT JOIN table3 ON (table1.CHYBAKOD=table3.CHYBAT)
LEFT JOIN table4 ON (table2.VYKONAT=table4.POPIS)
GROUP BY table1.ID

The problematic part is the group_concat and GROUP BY, but I need them to have unique ID in every row, because there is one to many relationship between table1 and table2.

allan

The key thing about Ajax is the first letter in the acronym - "Asynchronous". Your format function above is returning before the Ajax call is complete.

You need to do something more like this for it to work.

Allan

culter

Thaks Allan, I rewrited the code as you adviced. Problem is, that html is generated in the php script, which connect to the database and then send to index.php the json encoded "html".
My details.php script returns JSON in this form:

[
    {"TIME":"2015-05-14 01:22:45","ACTION":"Start"},
    {"TIME":"2016-05-23 02:53:16","ACTION":"Stop"}
]

and I need to show these values in the index.php. I'm not very familiar with javascript to modify it by myself. I think I need to modify the "success:" part. Please, can you show me how to display data from the JSON above in this function?

function format ( rowData ) {
        console.log(rowData);
    var div = $('<div/>')
        .addClass( 'loading' )
        .text( 'Loading...' );

$.ajax( {
    url: 'scripts/details.php',
    data: {
        name: rowData[0]
    },
    dataType: 'json',
    type: 'post',
    success: function ( json ) {
        div
            .html( json.html )
            .removeClass( 'loading' );
    }
} );
return div;

}

Thank you