Server side processing - JSON data displayed but table is not drawn

Elle3141

Hey all,

I managed to get DataTables working with client side processing, but as I will eventually have > 50,000 rows of data, I decided to try and implement server side processing, however it is not working for me correctly.

When I call my route, I see the data in a JSON format, but I am not seeing the table at all. It says "draw: 0", so I guess there is an issue with drawing the table? Do you guys have any idea why?

I have tried Googling this and have read the solutions of similar situations, to no avail.

Also, I am using Laravel 5.8 and in particular, the DataTables package for Laravel from: https://datatables.yajrabox.com/.

My JSON data, which I see when I call the route https://returns.jdoe.blah.test/returned-shipment/:

{"draw":0,"recordsTotal":11,"recordsFiltered":11,"data":[{"id":"1","customerNr":"98764","orderNr":"23478","pallet_id":"66788","status_id":"2","created_by":"Elle","created_at":"04 Jul 2019","updated_at":"2019-07-09 07:23:20"},{"id":"2","customerNr":"99999","orderNr":"22222","pallet_id":"22335","status_id":"1","created_by":"Sophie","created_at":"04 Jul 2019","updated_at":"2019-07-04 08:26:28"},{"id":"3","customerNr":"54657","orderNr":"89856","pallet_id":"11228","status_id":"1","created_by":"Wolfgang","created_at":"08 Jul 2019","updated_at":"2019-07-08 06:59:42"},{"id":"6","customerNr":"88879","orderNr":"33333","pallet_id":"33221","status_id":"1","created_by":"Matthias","created_at":"24 Jul 2019","updated_at":"2019-07-24 12:53:04"},{"id":"25","customerNr":"43793353","orderNr":"83961","pallet_id":null,"status_id":"1","created_by":"Sebastian","created_at":"26 Jul 2019","updated_at":"2019-07-26 12:08:43"},{"id":"26","customerNr":"11111","orderNr":"11111","pallet_id":"11111","status_id":"1","created_by":"Sara","created_at":"06 Aug 2019","updated_at":"2019-08-06 12:50:34"},{"id":"27","customerNr":"33333","orderNr":"33333","pallet_id":"33333","status_id":"1","created_by":"Lea","created_at":"06 Aug 2019","updated_at":"2019-08-06 12:52:00"},{"id":"28","customerNr":"44444","orderNr":"44444","pallet_id":"44444","status_id":"1","created_by":"Emilia","created_at":"06 Aug 2019","updated_at":"2019-08-06 12:52:22"},{"id":"29","customerNr":"55555","orderNr":"55555","pallet_id":"55555","status_id":"1","created_by":"Markus","created_at":"06 Aug 2019","updated_at":"2019-08-06 12:52:41"},{"id":"30","customerNr":"66666","orderNr":"66666","pallet_id":"66666","status_id":"1","created_by":"Daniel","created_at":"06 Aug 2019","updated_at":"2019-08-06 12:53:05"},{"id":"31","customerNr":"77777","orderNr":"77777","pallet_id":"77777","status_id":"1","created_by":"Ludwig","created_at":"06 Aug 2019","updated_at":"2019-08-06 12:53:31"}],"input":[]}

index.blade.php:

    <div class="row">
        <div id="tbl" class="col-sm-12">
            <table id="overview" class="cell-border display">

                <thead class="tbl-top">
                <tr>
                    <th>Retourennummer</th>
                    <th>Auftragsnummer</th>
                    <th>Datum</th>
                    <th>Zustand</th>
                </tr>
                </thead>

                <tfoot class="tbl-bottom">
                <tr>
                    <th>Retourennummer</th>
                    <th>Auftragsnummer</th>
                    <th>Datum</th>
                    <th>Zustand</th>
                </tr>
                </tfoot>
            </table>
        </div>
    </div>

    <script>
        $(document).ready(function () {
            var startingStatus = 'angelegt';
            var table = $('#overview').DataTable({
                "processing": true,
                "serverSide": true,
                "ajax": "{{ route('returned-shipment.index') }}",
                "columns": [
                    {data: 'id'},
                    {data: 'orderNr'},
                    {data: 'created_at'},
                    {data: 'status_id'}
                ],
                "search": {
                    "search": "angelegt"
                },
                "dom": "<'row'<'col-sm-4'l><'col-sm-8'f>>" +
                    "<'row'<'col-sm-12'tr>>" +
                    "<'row'<'col-sm-6'i><'col-sm-6'p>>",
                "paging": true,
                "info": true,
                "searching": true,
                "autoWidth": true,
                "language": {
                    "paginate": {
                        "previous": "Vorherige Seite",
                        "next": "Nächste Seite"
                    },
                    "search": "Suche:",
                    "info": "Zeige _START_ - _END_ von insgesamt _TOTAL_ Einträgen",
                    "lengthMenu": 'Einträge pro Seite' + '<br>' +
                        '<select class="custom-select mr-sm-2" id="inlineFormCustomSelect">' +
                        '<option selected value="10">10</option>' +
                        '<option value="20">20</option>' +
                        '<option value="30">30</option>' +
                        '<option value="40">40</option>' +
                        '<option value="50">50</option>' +
                        '<option value="-1">Alle</option>' +
                        '</select>'
                },
                initComplete: function () {
                    /**
                     * Drop-down filter is created for the 4th column "status" in the header and populates it with
                     * the different status values
                     */
                    this.api().columns([3]).every(function () {
                        var column = this;
                        var select = $('<select><option value="">alle</option></select>')
                            .appendTo($(column.header()))
                            .on('change', function () {
                                var val = $.fn.dataTable.util.escapeRegex(
                                    $(this).val()
                                );

                                column
                                    .search(val ? '^' + val + '$' : '', true, false)
                                    .draw();
                            });

                        /**
                         * When clicking on drop-down next to status, the sorting function is not activated
                         */
                        $(select).click(function (e) {
                            e.stopPropagation();
                        });

                        /**
                         * Once an option in the drop-down next to status has been selected, you can read the text in
                         * the drop-down
                         */
                        column.data().unique().sort().each(function (d, j) {
                            if (startingStatus === d) {
                                select.append('<option SELECTED value="' + d + '">' + d + '</option>')
                            } else {
                                select.append('<option value="' + d + '">' + d + '</option>')
                            }
                        });

                        /**
                         * When drop-down is clicked on, search field is cleared. Otherwise search field must be
                         * manually cleared before using the drop-down.
                         */
                        $(select).on('click', function () {
                            table.search(" ").draw();
                        });
                    });
                }
            });
        });
    </script>

ReturnedShipmentController:

    public function index()
    {
        return DataTables::of(ReturnedShipment::all())->make();
    }

web.php:

Route::get('returned-shipment', ['uses'=>'ReturnedShipmentController@index', 'as'=>'returned-shipment.index']);

If you need me to provide anything else, please let me know. I am pretty new to Laravel and DataTables.

Thanks in advance .

sandy

Hi Elle3141,

If you are implementing server side processing then the draw property in the JSON should never be 0. See the documentation for more details.

Hope this helps,

Sandy

Elle3141

@sandy Hi. Thank you for replying. I know that draw should not be 0, but I don't understand why it is. The JSON code I provided is my output, not my input. It's what I see instead of my table.

Do you have any idea why I am only seeing the JSON data instead of the table?

kthorngren

The draw parameter is a sequence number. Take a look at the XHR request headers in your browser's developer tools when you load the page. I believe the initial request will be sent with draw:1. Your server response needs the match the sequence number sent. In your case draw is 0 when Datatables is expecting 1.

Also you will notice each subsequent request the draw parameter will increment.

The doc that Sandy linked to describes in more detail the use of the draw parameter for both the request and response. You will need to update your server script to handle the draw parameter.

Kevin