Child row problem with searching box. SQLSTATE[42S22] Error

Damian86

I have a problem with the datatable child row. When searching with the search box I throw the following error.

The data was sent by ajax in a json file.
I am working with Laravel

This is the datatable code.

function format ( d ) {
    // `d` is the original data object for the row
    return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
        '<tr>'+
            '<td>ID:</td>'+
            '<td>'+d.id+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td><b>Dirección</b></td>'+
            '<td></td>'+
        '</tr>'+
        '<tr>'+
            '<td>Calle:</td>'+
            '<td>'+d.calle+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Número:</td>'+
            '<td>'+d.numero+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Interior:</td>'+
            '<td>'+d.interior+'</td>'+
        '</tr>'+
        '<tr>'+
            '<td>Código Postal:</td>'+
            '<td>'+d.codigopostal+'</td>'+
        '</tr>'+
        '</table>';
}

$(document).ready(function() {
    var table = $('#laravel_datatable').DataTable( {

        "processing": false,
        "serverSide": true,
        "responsive": true,
        "cache": true,
        "ajax": "{{ url('ine.list')}}",

        "language": {url: 'http://cdn.datatables.net/plug-ins/9dcbecd42ad/i18n/Spanish.json'},
        "scrollY": 400,
        "length" : 5000,
        "deferRender":    true,
        "scroller":       true,
        "columns": [
            {
                "className":      'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": ''
            },
            { "data": "nombre" },
            { "data": "apellidopaterno" },
            { "data": "apellidomaterno" },
            { "data": "fechanacimiento" },
            { "data": "actividad","bSortable": false }
        ],
        "order": [[1, 'asc']]
    } );

   
    $('#laravel_datatable tbody').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = table.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');
        }
    } );
} );


  </script>

colin

The error suggests there isn't a column ine_puebla in your table. As it's not mentioned above, that might be correct, so take a look at the server-side script and see why it's being requested.

Colin

Damian86

I do not use a script for the server-side. I use a package of laravel yajra / laravel-datatables, apparently, the error is there because when creating the JSON file you add this at the end and that is where you throw the error:

"queries": [
     {
       "query": "select count (*) as aggregate from (select '1' as` row_count` from `ine_puebla`) count_row_table",
       "bindings": [

       ],
       "time": "0.95"
     },
     {
       "query": "select * from` ine_puebla` ",
       "bindings": [

       ],
       "time": "3.96"
     }
   ],

colin

Yep, and there's that query. I'm not familiar with laravel yajra / laravel-datatables, it's not one of ours, but it seems it's been misconfigured. It would be worth posting on their forum or StackOverflow.

Colin

Damian86

It is possible that the yajra / laravel-datatables package has a problem. But with the only datatable that gives me that error is with the child row. With other datatables it doesn't give me that error.

kthorngren

The above error has nothing to do with the child detail rows. The child detail rows are independent of the Datatables search. You are using server side processing (serverSide: true) which is sending a ajax request to the server script (laravel yajra / laravel-datatables) to perform the search. As Colin says you will need to look at the laravel yajra / laravel-datatables environment to see why it is building an incorrect SQL query. Datatables is reporting the error as that is what the server script is returning to Datatables in the response.

Kevin