Laravel-datatables: 500 Server Error with 3000+ Data

[mohamedshuaau]

So I have a table full of data. About 3500 records. And I am using serverSide to load the data. But it is giving me DataTables warning: table id=data-table-all - Ajax error. For more information about this error, please see http://datatables.net/tn/7 error. If I reduce the amount of data on my table to 2000, it works as intended, and there is no error. I am not sure whats wrong here. Is it my query? Here is my query:

$array = Reservation::with('restaurantoption')->with('islandhost')->with('mealplan')->with('guest')->with('nationality')->with('restaurant')->with('user')->withCount('guestpaxes')->where('restaurant_id', $auth->restaurant_id)->latest()->get();
return datatables()->of($array)->toJson();

Route:

If I open the route in Chrome (Visit the Link) it shows all the data with no problem

Route::get('/reports/display-report-data/all-data', 'Reservation\ReportController@getAllReservations')->name('get.all.reservations.report');

And here is front page:

$('#data-table-all').DataTable({
                responsive: true,
                order:[[0,"dec"]],
                processing: true,
                serverSide: true,
                ajax: '{{ route('get.all.reservations.report') }}',
                columns: [
                    {data: 'id', name: 'id'},
                    {data: 'guest.name', name: 'guest'},
                    {data: 'room_no', name: 'room_no'},
                    {data: 'table_no', name: 'table_no', defaultContent: "null"},
                    {data: 'guestpaxes_count', name: 'guestpaxes_count'},
                    {data: 'status', name: 'status'},
                    {data: 'nationality.name', name: 'nationality'},
                    {data: 'mealplan.name', name: 'mealplan'},
                    {data: 'islandhost.name', name: 'islandhost', defaultContent: ""},
                    {data: 'restaurant.name', name: 'restaurant'},
                    {data: 'restaurantoption.name', name: 'restaurantoption', defaultContent: ""},
                    {data: 'reservation_date', name: 'reservation_date'},
                    {data: 'reservation_time', name: 'reservation_time'},
                    {data: 'free_meal', name: 'free_meal'},
                    {data: 'served_by', name: 'served_by', defaultContent: ""},
                    {data: 'user.name', name: 'user', defaultContent: "null"},
                    {data: 'cancel_reason', name: 'cancel_reason'},
                    {data: 'canceled_by', name: 'canceled_by'},
                    {data: 'created_at', name: 'created_at'},
                ],
                "createdRow": function (row, data) {

                    $('td', row).eq('5').html($('<span>').attr({
                        class:  (data.status == 1) ? 'text-success' : ( (data.status == 2) ? 'text-danger': 'text-warning' )
                    }).html((data.status == 1) ? 'Confirmed' : ( (data.status == 2) ? 'Denied': 'Pending' )));

                    $('td', row).eq('13').html($('<span>').attr({
                        class:  ((data.free_meal == 1) ? 'text-success': 'text-danger' )
                    }).html((( (data.free_meal == 1) ? 'YES': 'NO' ))));

                }
            });
        });

I know it's a bit messy sorry. If I remove /comment the serverSide, it works normally but very slow (Obviously because it is loading all data on load).

The error on console is very very long but I will paste it here anyways (Please have mercy sensei):

http://test.com/restaurant-reservations/reports/display-report-data/all-data?draw=1&columns%5B0%5D%5Bdata%5D=id&columns%5B0%5D%5Bname%5D=id&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=guest.name&columns%5B1%5D%5Bname%5D=guest&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=room_no&columns%5B2%5D%5Bname%5D=room_no&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=table_no&columns%5B3%5D%5Bname%5D=table_no&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=true&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=guestpaxes_count&columns%5B4%5D%5Bname%5D=guestpaxes_count&columns%5B4%5D%5Bsearchable%5D=true&columns%5B4%5D%5Borderable%5D=true&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=status&columns%5B5%5D%5Bname%5D=status&columns%5B5%5D%5Bsearchable%5D=true&columns%5B5%5D%5Borderable%5D=true&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B6%5D%5Bdata%5D=nationality.name&columns%5B6%5D%5Bname%5D=nationality&columns%5B6%5D%5Bsearchable%5D=true&columns%5B6%5D%5Borderable%5D=true&columns%5B6%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B6%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B7%5D%5Bdata%5D=mealplan.name&columns%5B7%5D%5Bname%5D=mealplan&columns%5B7%5D%5Bsearchable%5D=true&columns%5B7%5D%5Borderable%5D=true&columns%5B7%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B7%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B8%5D%5Bdata%5D=islandhost.name&columns%5B8%5D%5Bname%5D=islandhost&columns%5B8%5D%5Bsearchable%5D=true&columns%5B8%5D%5Borderable%5D=true&columns%5B8%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B8%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B9%5D%5Bdata%5D=restaurant.name&columns%5B9%5D%5Bname%5D=restaurant&columns%5B9%5D%5Bsearchable%5D=true&columns%5B9%5D%5Borderable%5D=true&columns%5B9%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B9%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B10%5D%5Bdata%5D=restaurantoption.name&columns%5B10%5D%5Bname%5D=restaurantoption&columns%5B10%5D%5Bsearchable%5D=true&columns%5B10%5D%5Borderable%5D=true&columns%5B10%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B10%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B11%5D%5Bdata%5D=reservation_date&columns%5B11%5D%5Bname%5D=reservation_date&columns%5B11%5D%5Bsearchable%5D=true&columns%5B11%5D%5Borderable%5D=true&columns%5B11%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B11%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B12%5D%5Bdata%5D=reservation_time&columns%5B12%5D%5Bname%5D=reservation_time&columns%5B12%5D%5Bsearchable%5D=true&columns%5B12%5D%5Borderable%5D=true&columns%5B12%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B12%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B13%5D%5Bdata%5D=free_meal&columns%5B13%5D%5Bname%5D=free_meal&columns%5B13%5D%5Bsearchable%5D=true&columns%5B13%5D%5Borderable%5D=true&columns%5B13%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B13%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B14%5D%5Bdata%5D=served_by&columns%5B14%5D%5Bname%5D=served_by&columns%5B14%5D%5Bsearchable%5D=true&columns%5B14%5D%5Borderable%5D=true&columns%5B14%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B14%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B15%5D%5Bdata%5D=user.name&columns%5B15%5D%5Bname%5D=user&columns%5B15%5D%5Bsearchable%5D=true&columns%5B15%5D%5Borderable%5D=true&columns%5B15%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B15%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B16%5D%5Bdata%5D=cancel_reason&columns%5B16%5D%5Bname%5D=cancel_reason&columns%5B16%5D%5Bsearchable%5D=true&columns%5B16%5D%5Borderable%5D=true&columns%5B16%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B16%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B17%5D%5Bdata%5D=canceled_by&columns%5B17%5D%5Bname%5D=canceled_by&columns%5B17%5D%5Bsearchable%5D=true&columns%5B17%5D%5Borderable%5D=true&columns%5B17%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B17%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B18%5D%5Bdata%5D=created_at&columns%5B18%5D%5Bname%5D=created_at&columns%5B18%5D%5Bsearchable%5D=true&columns%5B18%5D%5Borderable%5D=true&columns%5B18%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B18%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=dec&start=0&length=10&search%5Bvalue%5D=&search%5Bregex%5D=false&_=1578940884709

What could be the problem here? I have searched all over Git issues and Stackoverflow and I could not find anything relating to my issue.

[yajra]

Don't use collection for large datasets. Remove get().

$array = Reservation::with('restaurantoption')
->with('islandhost')
->with('mealplan')->with('guest')->with('nationality')
->with('restaurant')->with('user')
->withCount('guestpaxes')
->where('restaurant_id', $auth->restaurant_id)
->latest();

return datatables()->of($array)->toJson();

[mohamedshuaau]

Hello

Thank you so much. This solves my issue. I had one more issue I noticed after removing get. On my local server, I get the data instantly. It doesn't even take a second. But on my Digital Ocean server, it takes about 6 seconds to load. When I SSH and Htop the server, it shows that when requested, mySQL takes 100% usage from CPU to complete one single request. Is it because my query is too big?

[benyaminl]

Hello

Thank you so much. This solves my issue. I had one more issue I noticed after removing get. On my local server, I get the data instantly. It doesn't even take a second. But on my Digital Ocean server, it takes about 6 seconds to load. When I SSH and Htop the server, it shows that when requested, mySQL takes 100% usage from CPU to complete one single request. Is it because my query is too big?

that could be the case, try to toSql() and run the resulted query first. and see does it also happen if you do normal query? If yes then you should find another way to query the data. Also if you use agregate, try to use indexes(on mysql, I don't know on other database engine).