Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

xereah's avatar

Laravel Datatables long load data above 10k rows without serverside

Hi, when generating more than 10,000 records from the database without serverside:true, the page takes a long time to load. With the serverside turned on, everything is fast, but I can't filter the database by date range. What could be the problem? Is it possible to speed it up somehow, or how to filter by date range when server side is on? Code below.

public function index(Request $request)

    {
        if ($request->ajax()) {
            $data = DB::table('jobs')
            ->join('companies', 'companies.id', '=', 'jobs.fk_company')
            ->join('task_type', 'task_type.id', '=', 'jobs.fk_tasktype')
            ->join('users', 'users.id', '=', 'jobs.fk_user')
            ->select('jobs.*', 'companies.shortcode','users.name','users.surname','task_type.name');
            return Datatables::of(Job::query())
            ->editColumn('fk_typetask', function ($job) {
                return '<a class="text-success" data-toggle="modal" id="mediumButton" data-target="#mediumModal"
                data-attr="'.url('/job/editone', $job->id).'">'.$job->fk_typetask.'</a>';
            })
            ->editColumn('order', function ($job) {
                return '<a class="text-success" data-toggle="modal" id="mediumButton" data-target="#mediumModal"
                data-attr="'.route('admin.jobs.edit', $job->id).'">'.$job->order.'</a>';
            })
            ->editColumn('fk_user', function ($job) {
                return  $job->user->surname.' '. $job->user->name;
            })
            ->editColumn('start_date', function ($job) {
                return  $job->start_date;
            })
            ->editColumn('end_date', function ($job) {
                return  $job->end_date;
            })
            ->editColumn('fk_tasktype', function ($job) {
                return  $job->task_type->name;
            })
            ->editColumn('fk_company', function ($job) {
                return  $job->company->shortcode;
            })
            
                ->addIndexColumn()
                ->rawColumns(['fk_typetask','order','fk_user','start_date','end_date','fk_tasktype'])
                ->order(function ($query) {
                    if (request()->has('order')) {
                        $query->orderBy('order', 'desc');
                    }
                })
                ->make(true);
        }            
        
        $filter_typetask = TypeTask::all()->pluck('name')->unique();
        $filter_tasktype = TaskType::all()->pluck('name')->unique();
        $filter_company = Company::all()->pluck('shortcode')->unique();
        $filter_user = User::all();     
        $user_all = User::all();
        return view('admin.jobs.test', compact('filter_user','filter_typetask','filter_tasktype','filter_company','user_all'));
        
    }
<script type="text/javascript">
  $(function () {
  
    
    var table = $('.yajra-datatable').DataTable({
        serverSide: true,
        paging: true,
        processing:true,
        orderClasses: false,
        responsive: true,
        deferRender: true,
        renderer: "bootstrap",
        colReorder:true,
       
        search: {
            return: true,
        },
        stateSave: true,
        deferRender: true,
      
        ajax: "{{ url('index') }}",
        columnDefs: [ {
                "targets": 5,
                "orderable": false,
                "searchable": false,
                } ],
        columns: [
            {data: 'fk_typetask', name: 'fk_typetask'},
            {data: 'fk_tasktype', name: 'fk_tasktype'},
            {data: 'order', name: 'order'},
            {data: 'fk_company', name: 'fk_company'},
            {data: 'fk_user', name: 'fk_user'},
            { data: null,
                render: function (data, type, row) {
                var duration = moment.duration(moment(data.end, "HH:mm").diff(moment(data.start, "HH:mm")));
                //return duration.get("hours") +":"+ duration.get("minutes") +":"+ duration.get("seconds");
                return moment.utc(duration.asMilliseconds()).format("HH:mm");
                }  
            },
            {data: 'start_date',name: 'start_date'},                
            {data: 'end_date',name: 'end_date'},               
            {data: 'rns', name: 'rns'},
            {data: 'description', name: 'description'},          
           
        ],
        
    });

    $('#filter_tasktype').on( 'keyup', function () {
      
    table.columns(0).search( this.value ).draw();
    } );
    $('#filter_typetask').on('change', function () {
    table.columns(1).search( this.value ).draw();
    } );
    $('#filter_order').on('keyup', function () {
    table.columns(2).search( this.value ).draw();
    } );
    $('#filter_company').on('change', function () {
    table.columns(3).search( this.value ).draw();
    } );
    $('#filter_user').on('change', function () {
    table.columns(4).search( this.value ).draw();
    } );
    $('#filter_decription').on( 'keyup', function () {
    table.columns(9).search( this.value ).draw();
    } );
    $('#filter_rns').on( 'keyup', function () {
    table.columns(8).search( this.value ).draw();
    } );
 
    
  });
0 likes
4 replies
Tray2's avatar

What do you expect, 10000 records is a lot, and it will take time and use up memory.

Why do you think you need to fetch all of those records in one go? I suggest using pagination and fetching portions from the database instead.

1 like
jlrdw's avatar

Use queries on server side with ORDER BY. Still paginate.

My suggestion is to forget about these data tables and write your own HTML tables.

I have written my own for years and can add on the fly and in place edit.

Take the learning curve to learn how and you will ask yourself "why did I even mess with a data table".

1 like

Please or to participate in this conversation.