empty result set yajra datatable on large tables

[ahmadbadpey]

I have a Question Model by about 30,000 records like this:

class Question extends Model
    {
        use SoftDeletes;

        protected $primaryKey = 'question_id';

        protected $fillable   = ['text', 'code', 'cat', 'answer', 'confirmed', 'is_private', 'email', 'parent', 'expert'];

        public function sub_questions ()
        {
            return $this->hasMany(Question::class, 'parent', 'question_id');
        }

        public function latest_sub_question ()
        {
            return $this->hasOne(Question::class, 'parent', 'question_id')->latest();
        }


    }

To show a list of all questions I'm using yajra datatable laravel package.

This is my js code :

var allQuestionsTable =
$('#allQuestionsTable').DataTable({
                        processing: true,
                        serverSide: true,
                        "bSort": false,
                        "responsive": true,
                        ajax: {
                            url: '{!! route('admin.questionsDatatable') !!}'
                        },
                        columns: [
                            {data: 'checkbox'},
                            {data: 'code', name: 'code'},
                            {data: 'text', name: 'text'},
                            {data: 'created_at'},
                            {data: 'answer', name: 'answer'},
                            {data: 'expert'},
                            {data: 'confirmed'},
                            {data: 'actions'}
                        ]
                    });

And in the back-end I wrote this function:

public function questionsDatatable (Request $request)
        {
            $questions = Question::with('latest_sub_question')->whereNull('parent');

            $datatable = app('datatables')->of($questions)
                ->addColumn('checkbox', '<input type="checkbox" name="item_id[]" value="{{$question_id}}" id="Check_{{$question_id}}">');

                //other column modifications comes here

            return $datatable->make(true);
        }

First when my table had A few rows all things worked fine but when I added a large number of new records,questionsDatatable return empty value and cause datatables return an alert error.

what is problem ? and is there any parameter that can use to solve this problem?

[yajra]

Have you tried inspecting the ajax request? It should give you an error dump on what's causing the issue.

[ahmadbadpey]

@yajra , Yes I did but just an empty response and 500 Internal Server Error status code returned.

of course between fetching questions and before calling app('datatables') for some reason to iterate result I should use get() method and then use each() method on $questions . in fact my backend code is :

public function questionsDatatable (Request $request)
        {
            $questions = Question::with('latest_sub_question')->whereNull('parent');
             $questions = $questions->get();

			$questions = $questions->map(function ($item, $key) {
				return is_null($item->latest_sub_question) ? $item : $item->latest_sub_question;
			});

			$questions->sortByDesc('created_at');

            $datatable = app('datatables')->of($questions)
                ->addColumn('checkbox', '<input type="checkbox" name="item_id[]" value="{{$question_id}}" id="Check_{{$question_id}}">');

                //other column modifications comes here

            return $datatable->make(true);
        }

I do not know that is a reason for the error or not?

[yajra]

You are indeed loading all your records which would definitely eat up a lot of your resources. I suggest you remove get. Try something like below.

public function questionsDatatable (Request $request)
{
    $questions = Question::with('latest_sub_question')->whereNull('parent');
    $datatable = app('datatables')->of($questions)
        ->addColumn('checkbox', '<input type="checkbox" name="item_id[]" value="{{$question_id}}" id="Check_{{$question_id}}">');
        ->editColumn('latest_sub_question', function($item) {
            return is_null($item->latest_sub_question) ? $item : $item->latest_sub_question;
        })
        //other column modifications comes here

    return $datatable->make(true);
}