Laravel Yajra Datatable Crashed While Loading Huge Data

Question

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.

`to load 172.425 record` - gigant problem... You should implement limit&offset (pagination) and load only those which needed for 1 page — InDevX, Mar 09 '23 at 07:59
I'm using a serverside datatable which automatically add pagination to the table — Yusuf Giovanno, Mar 09 '23 at 08:09
Nope, `DB::table('royalti.testapi')->get();` - loading all data. If u wanna auto-limiting try doing like [docs](https://yajrabox.com/docs/laravel-datatables/master/quick-starter#setup-users-datatable) says or try to pass model and remove `->get()`, like `$data = TestAPI::query();` — InDevX, Mar 09 '23 at 08:17

Sumit kumar · Accepted Answer · 2023-03-09T09:16:06.817

2

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

edited Mar 09 '23 at 09:16

answered Mar 09 '23 at 09:09

Sumit kumar

402
2
10

Thanks! it helps a lot and worked fine, but i found another issue, in my prev code, I have the addColumn function for action button, how can I achieve this with your answer kind of code? thanks also some columns are showing "0X" and "https://0x.org/api" instead of the real data, and how about the DT_Index? do you happen to have any documentation? sorry for the problem – Yusuf Giovanno Mar 09 '23 at 09:43
0x not sure seems like its working with some third party api check your database as well and debug. the post mentioned the basic steps to achieve your goal, also lmk if you're asking that you want to add a column into table for actions? – Sumit kumar Mar 09 '23 at 10:16
the data is already fixed, it's my database that's unproper. I want to add an action column in my datatable for edit function which is returning a button with data-id="primary key of the table"... and also, my individual column search is not working now. – Yusuf Giovanno Mar 09 '23 at 10:41
add header in table ``Action`` and in datatable code add another object in columns array like this: ````{ data: null, render: function (data, type, row, meta) { return 'return ' ';';}}```` – Sumit kumar Mar 09 '23 at 11:20
I've fixed the action column, now the only thing that's left is that my column search bar is not working. – Yusuf Giovanno Mar 09 '23 at 11:28
not sure about it the datatables search is working on my end and not able to replicate your code right now. – Sumit kumar Mar 09 '23 at 11:34
1

alright then, thanks for everything, mate... really usefull for me... – Yusuf Giovanno Mar 09 '23 at 11:46

score 1 · Answer 2 · answered Mar 09 '23 at 08:07

1

Loading all the records is not a smart thing to do

To improve the performance, it is better to use the paging feature

Also You can also reduce the amount of data by reducing the return columns

answered Mar 09 '23 at 08:07

Aryan Hasanzadeh

335
1
8

Laravel Yajra Datatable Crashed While Loading Huge Data

2 Answers2