phplaravelyajra-datatable

Laravel Yajra Datatable Crashed While Loading Huge Data


I'm having trouble using Laravel Yajra Datatable to load 172.425 record with the following error:

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4096 bytes) in vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 421.

I already tried to set my php ini memory limit from 128M to 512M then clear my cache and config using artisan command but nothing changes.

here's my code:

The Table

<div class="table-responsive">
        <table class="table table-striped" id="table_employee">
            <thead>
                <tr>
                    <th>No</th>
                    <th>Action</th>
                    <th>API</th>
                    <th>Description</th>
                    <th>Cors</th>
                    <th>Link</th>
                </tr>
            </thead>
            <thead>
                <tr>
                    <th></th>
                    <th></th>
                    <th class="th">API</th>
                    <th class="th">Description</th>
                    <th class="th">Cors</th>
                    <th class="th">Link</th>
                </tr>
            </thead>
            <tbody></tbody>
        </table>
    </div>

My JQuery for the Datatable

$(document).ready(function () {
    $('#table_employee').DataTable({
        processing  : true,
        serverSide  : true,
        responsive  : true,
        dom         : 'Bfrtip',
        initComplete: function() {
            this.api().columns().every(function() {
                var that = this;

                $('input', this.footer()).on('keyup change clear', function() {
                    if (that.search() !== this.value) {
                        that.search(this.value).draw();
                    }
                });
            });
        },
        ajax: "/api/getdata",
        columns: [
            {data  : 'DT_RowIndex', name  : 'DT_RowIndex'},
            {data  : 'action',      name  : 'action', orderable: false, searchable: false},
            {data  : 'API',         name  : 'API'        },
            {data  : 'Description', name  : 'Description'},
            {data  : 'Cors',        name  : 'Cors'       },
            {data  : 'Link',        name  : 'Link'       },
        ]
    });

    $('#table_employee thead .th').each(function() {
            var title = $(this).text();
            $(this).html('<input type="text" class="form-control rounded shadow" placeholder="search" />');
            
        });
});

and here's my controller:

function getdata(){

    $data = DB::table('royalti.testapi')->get();

    return DataTables::of($data)->addIndexColumn()
            ->addColumn('action', function($row){
                $btn = '<a href="javascript:void(0)" class="btn btn-info text-white btn-sm">' . $row->Cors . '</a>';
                return $btn;
            })
            ->rawColumns(['action'])
            ->make(true);
}

I run this program using my own personal laptop with 8GB of RAM. Any help would be appreciated.


Solution

  • You're doing get() so your controller fetching all the data first and then sending the data into yajra/datatable on frontend.

    you can do it like this(enable paging:true in dt on frontend):

    $model = ModelName::query();
    $dt = new DataTables();
    return $dt->eloquent($model)->toJson();
    

    paging:true fetches only data to be displayed on datatable pagination. Do it like this for using db facades:

    $users = DB::table('tableName');
    $dt = new DataTables();
    return $dt->query($users)->toJson(); //datatables will do the rest