CODEWITHSUNDEEP
phpjquerylaravel-5datatables
hu7sy
hu7sy

Reputation: 961

Using yajra data tables for server side data-tables

i am using yajra data tables for server side data-tables my controller is this 

public static function alleventsData(Request $request)
{
    $limit = intVal($request->input('length'));
    $start = $request->input('start');
    $meta = EventsRepository::showMeta();
    $totalRecords = $meta[1][1]['Value'];
    $offset = intVal($start);
    $allEvents = EventsRepository::allEvents($offset, $limit);
    return Datatables::collection($allEvents)
    ->addColumn(
        'parent',
        function ($allEvents) {
        return $allEvents['category_name'];
        }
    )
    ->addColumn(
        'venueName',
        function ($allEvents) {
        return $allEvents['venue_name'];
        }
    )
    ->addColumn(
        'venueLocation',
        function ($allEvents) {
        return $allEvents['location'];
        }
    )
    ->addColumn(
        'occurs_at',
        function ($allEvents) {
        return $allEvents['occurs_at'];
        }
    )
    ->addColumn(
        'hot_popular_main',
        function ($allEvents) {
        return '<input type="checkbox" name="hot_popular_main" class= "updatePopertyEvent" attr="hot_popular_main" id="'.$allEvents['id'].'" value="'.$allEvents['hot_popular_main'].'"  '.($allEvents['hot_popular_main']==1?'checked="checked"':'').'/>';
        }
    )
    ->addColumn(
        'synchronize',
        function ($allEvents) {
            return '<button value="'.$allEvents['id'].'" class="btn btn-info synchronize" >Synchronize</button>';
        }
    )
    ->addColumn(
        'status',
        function ($allEvents) {
            $status = $allEvents['status']==1?"Active":"Deactive";
            return '<button value="'.$allEvents['id'].'" class="btn btn-info status" data-attr="'.$allEvents['status'].'">'.$status.'</button>';
        }
    )
    ->with(['recordsTotal'=>$totalRecords, 'recordsFiltered'=>$totalRecords])
    ->make(true);
}

and my js is this 

    $(function() {
    $('.eventTableAll').DataTable({
        processing: true,
        serverSide: true,
        ajax: '{!! route('datatables.alleventsData') !!}',
        columns: [
            { data: 'event_name', name: 'event_name' },
            { data: 'parent', name: 'parent', searchable: true },
            { data: 'venueName', name: 'venueName', searchable: true },
            { data: 'venueLocation', name: 'venueLocation', searchable: true },
            { data: 'occurs_at', name: 'occurs_at', searchable: true },
            { data: 'hot_popular_main', name: 'hot_popular_main' },
            { data: 'synchronize', name: 'synchronize' },
            { data: 'status', name: 'status' }

        ]
    });
});

but problem is when i move to next page like on second it does not get any data , i have seen console that it is getting data but not embedded in data-tables data index .

Upvotes: 2

Views: 3754

Answers (1)

Akash M. Pai
Akash M. Pai

Reputation: 81

I faced the exact same issue 2 days ago. Here you are fetching records with limit & offset manually and using Datatables::collection().

First if you have enabled csrf for forms in laravel settings:

  1. Disable it. Comment \App\Http\Middleware\VerifyCsrfToken::class in Kernel.php

  2. Or set csrf in ajax header before the datatable js function

    $.ajaxSetup({
    headers: { 
       'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
    }
});

If this is done. And still not working then proceed further.

Solution 1: Just before you pass the fetched data to Datatables::collection() use this:

$allEvents = collect(array_pad($allEvents->toArray(), -($iDisplayStart + count($allEvents->toArray())), 0));

This line gives you data in response of Datatables::collection() something like this: For page 1: data = [ 0=>[event_0], 1=>[event_1], ... limit=>[event_limit] ];

For page n: data = [ 0=>[], 1=>[], ... offset-1=?[], offset=>[event_0], offset+1=>[event_1], ... offset+limit-1=>[event_limit] ];

Nothing but creating empty indexes before the offset in the collection. This is because the collection() or of() [or any other prebuilt functions] consider the $iDisplayStart and looks for data in the collection starting from where index=$iDisplayStart.

And finally

return Datatable::of($allEvents)
    ->with(['recordsTotal' => $allEventsCount, 'recordsFiltered' => $allEventsCount, 'start' => $iDisplayStart])
    ->make();

Solution 2: Instead of manually implementing pagination use prebuilt functions of yajra:

return Datatables::of($allEvents)
    ->filter(function ($allEvents) use ($request, $iDisplayLength, $iDisplayOffset) {
        $allEvents->skip(($iDisplayOffset) * $iDisplayLength)
            ->take($iDisplayLength);
        return $allEvents;
    })
    ->with(['recordsTotal' => $allEventsCount, 'recordsFiltered' => $allEventsCount, 'start' => $iDisplayStart])
    ->make();

Refer: https://datatables.yajrabox.com/eloquent/post-column-search

NOTE: If the above solutions doesn't work, take care of all these things :

  • check if you have included the latest supported version of datatables js library for yajra
  • check if you getting the data from database for the specified limit
  • check if the ajax datatables call you are making is of type POST
  • try by toggling these ajax parameters to true/false: processing,serverSide. these parameters are mandatory to be set true if ajax parameter is send in some older versions of datatables library
  • check if you have these lines in the return Datatable in both the case ->with(['recordsTotal' => $allEventsCount, 'recordsFiltered' => $allEventsCount, 'start' => $iDisplayStart])
  • note that the $allEventsCount is the count of all records in database. You need to get it manually using ->count()

Solution 3:

If the above solutions donot work, The last solution would be to drop Yajra and use client side datatables and code for pagination using datatables.js. This doesn't give you performance drop but you will need to do more client side coding. Caching is also available for client-side datatables.

Refer: https://datatables.net/examples/server_side/pipeline.html
datatables.net/examples/data_sources/server_side.html

Upvotes: 3

Related Questions