#StackBounty: #mysql #laravel #datatable #server-side Laravel datatable taking too much of time to load data

Bounty: 50

Here loading orders data using below code but it takes too much time to load and I’m also using serverSide and there are only 320 data’s something and TTFB are very high and I also tried "pageLength": 30, "stateSave": true, and using Cloudflare and Nginx server but the result is same please let me know how I fix this issue. I tried most of the solutions but still having issues.

Controller.php

$orders = new Order;
        $search = $request->search['value'];
        $filter_type = $request->filter_type;
        if ($filter_type == "custom") {
            $from = date('Y-m-d' . ' 00:00:00', strtotime($request->from_dates));
            if ($request->has('to_dates')) {
                $to = date('Y-m-d' . ' 23:59:59', strtotime($request->to_dates));
                $orders = $orders->whereBetween('created_at', array($from, $to));
            }
        }
        elseif ($filter_type == "daily") {
            $orders = $orders->where('created_at', '>=', CarbonCarbon::today());
        }
        elseif ($filter_type == "weekly") {
            $fromDate = CarbonCarbon::now()->subDay()->startOfWeek()->toDateString();
            $tillDate = CarbonCarbon::now()->subDay()->endOfWeek()->toDateString();
            $orders = $orders->whereBetween(DB::raw('date(created_at)'), [$fromDate, $tillDate]);
        }
        elseif ($filter_type == "monthly") {
            $orders = $orders->whereRaw('MONTH(created_at) = ?', [date('m')]);
        }
        elseif ($filter_type == "yearly") {
            $orders = $orders->whereRaw('YEAR(created_at) = ?', [date('Y')]);
        }
        $orders = $orders->orderByDesc('id')->select();
        $orders = $orders->get();

        $datatable = DataTables::of($orders)
            ->addColumn('id', function ($orders) {
                return @$orders->id;
            })
            ->addColumn('payment_type', function ($orders) {
                return @$orders->payment_type_text;
            })
            ->addColumn('user_name', function ($orders) {
                return @$orders->user->name;
            })
            ->addColumn('store_name', function ($orders) {
                return @$orders->store->name;
            })
            ->addColumn('service_type', function ($orders) {
                return @$orders->store->service_type1->service_name;
            })
            ->addColumn('total', function ($orders) {
                return html_entity_decode(currency_symbol() . (@$orders->total_amount+@$orders->wallet_amount));
            })
            ->addColumn('status_text', function ($orders) {
                return @$orders->status_text;
            })
            ->addColumn('action', function ($orders) {
                return '<a title="' . trans('admin_messages.view') . '" href="' . route('admin.view_order', $orders->id) . '" ><i class="material-icons">edit</i></a>';

            });
        $columns = ['id', 'payment_type', 'user_name', 'store_name', 'total', 'status_text'];

        $base = new DataTableBase($orders, $datatable, $columns, 'Orders');
        return $base->render(null);

Blade.php

@extends('admin/template')
@section('main')
<?php flush(); ?>
<div class="content" ng-controller="statements" ng-cloak>
        <div class="card">

<div class="card-header card-header-rose card-header-text">
                  <div class="card-text">
                    <h4 class="card-title">{{$form_name}}</h4>
                  </div>
                </div>
                  <div class="card-body ">
            <div class="table-responsive">
                <table id="statement_table" class="table table-condensed w-100">
                </table>
            </div>
        </div>
        </div>
    </div>
</div>
@endsection

@push('scripts')
<link rel="stylesheet" href="{{asset('admin_assets/css/buttons.dataTables.css')}}">
http://asset('admin_assets/js/dataTables.buttons.js')
http://url('vendor/datatables/buttons.server-side.js')

<script>

    var column = [
    {data: 'id', name: 'id', title: '{{trans("admin_messages.order_id")}}' },
    {data: 'payment_type',name: 'payment_type',title: '{{trans("admin_messages.payment_type")}}',searchable: true},
    {data: 'user_name',name: 'user_name',title: '{{trans("admin_messages.user_name")}}'},
    {data: 'user_address',name: 'user_address',title: '{{trans("admin_messages.address")}}'},
    {data: 'mobile_number',name: 'mobile_number',title: '{{trans("admin_messages.mobile_number")}}'},
    {data: 'store_name',name: 'store_name',title: '{{trans("admin_messages.store_name")}}'},
    {data: 'service_type',name: 'service_type',title: '{{trans("admin_messages.service_type")}}'},
    {data: 'total',name: 'total',title: '{{trans("admin_messages.total")}}'},
    {data: 'status_text',name: 'status_text',title: '{{trans("admin_messages.order_status")}}'},
    {data: 'created_at',name: 'created_at',title: '{{trans("admin_messages.created_at")}}'},
    {data: 'action',name: 'action',title: '{{trans("admin_messages.action")}}',orderable: false,searchable: false}
    ];

  var oTable = $('#statement_table').DataTable({
    dom:"lBfrtip",
    buttons:["csv","excel","print"],
    order:[0, 'desc'],
    processing: true,
    serverSide: true,
    
    ajax: {
      url: ajax_url_list['all_orders'],
      data: function (d) {
        d.filter_type = $('#filter_by').val();
        d.from_dates = $('#from_date').val();
        d.to_dates = $('#to_date').val();
      }
    },
    columns: column
  });
</script>
@endpush

Create Table

CREATE TABLE `order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `store_id` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `driver_id` int(11) DEFAULT NULL,
  `recipient` tinyint(4) DEFAULT NULL,
  `subtotal` decimal(11,2) DEFAULT NULL,
  `offer_percentage` decimal(11,2) DEFAULT NULL,
  `offer_amount` decimal(11,2) DEFAULT NULL,
  `promo_id` int(11) DEFAULT NULL,
  `promo_amount` decimal(11,2) DEFAULT NULL,
  `delivery_fee` decimal(11,2) DEFAULT NULL,
  `booking_fee` decimal(11,2) DEFAULT NULL,
  `store_commision_fee` decimal(11,2) DEFAULT NULL,
  `driver_commision_fee` decimal(11,2) DEFAULT NULL,
  `tax` decimal(11,2) DEFAULT NULL,
  `total_amount` decimal(11,2) DEFAULT NULL,
  `wallet_amount` decimal(11,2) DEFAULT NULL,
  `payment_type` tinyint(4) DEFAULT NULL,
  `owe_amount` decimal(11,2) DEFAULT NULL,
  `store_owe_amount` decimal(11,2) DEFAULT NULL,
  `applied_owe` decimal(11,2) DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `payout_status` tinyint(4) DEFAULT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `est_preparation_time` time DEFAULT NULL,
  `est_travel_time` time DEFAULT NULL,
  `est_delivery_time` time DEFAULT NULL,
  `cancelled_by` tinyint(4) DEFAULT NULL,
  `cancelled_reason` int(10) unsigned DEFAULT NULL,
  `cancelled_message` text COLLATE utf8mb4_unicode_ci,
  `delay_min` time DEFAULT NULL,
  `delay_message` text COLLATE utf8mb4_unicode_ci,
  `schedule_status` tinyint(4) DEFAULT '0',
  `payout_is_create` tinyint(4) DEFAULT '0',
  `schedule_time` timestamp NULL DEFAULT NULL,
  `notes` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_notes` text COLLATE utf8mb4_unicode_ci,
  `store_notes` text COLLATE utf8mb4_unicode_ci,
  `driver_notes` text COLLATE utf8mb4_unicode_ci,
  `declined_at` timestamp NULL DEFAULT NULL,
  `accepted_at` timestamp NULL DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `delivery_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `delivery_type` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tips` decimal(11,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_store_id_foreign` (`store_id`),
  KEY `order_user_id_foreign` (`user_id`),
  CONSTRAINT `order_store_id_foreign` FOREIGN KEY (`store_id`) REFERENCES `store` (`id`),
  CONSTRAINT `order_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10268 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

enter image description here


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.