Slow loading yajra / laravel-datatables

2

Summary of problem or feature request

Well I have a problem using DataTable in laravel, so I saw the problem is because I have a relationship and when calling the values of the relationship this is the one with an excessive delay

I have 1300 records

I have a data load delay of 3-5 sec.

When I stop using ajax to load the data and the normal method (without Server-inside), it is very fast even with the table relationship.

Any ideas so that you can optimize the code? Thank you in advance

Code snippet of problem

Controller

public function list_user(){
$users = User::all();
$users->each(function ($users)
{
    $users->role;
});
return datatables()->collection($users)->toJson();
}

Js

function activar_tabla_users() {
$('#DataTableUser').DataTable({
    "processing" : true,
    "serverSide" : true,
    "searchDelay" : 500,
    "responsive": {
        orthogonal: 'responsive'
    },
    "language": {
        "url": '{!! asset('/plugins/datatables.net/latino.json') !!}'
    } ,
    "lengthMenu": [5,10, 25, 50, 75 ],
    "ajax":'{!! url('admin/list_user') !!}',
    columns: [
        {data: 'id' },
        {data: 'username'},
        {data: 'name',
            render: function (data, type, row, meta) {
                return row.name + ' ' + row.lastname;
            } 
        },
        {data: 'email'},
        {data: 'role.name',
            render: function(data, type, row, meta) {
            var html = ''
            if ( row.role.name == 'Administrador' )
            {
                html = '<span class="label label-danger" > <label style="width:80px;"> '+row.role.name+' </label></span>';

            }else {
                html = '<span class="label label-primary" > <label style="width:80px;"> '+row.role.name+' </label></span>';
            }
            return html;
            }
        }
        }],

});
}
activar_tabla_users(); 
    
asked by Luis Aguilar 27.09.2018 в 05:07
source

1 answer

1

In order for the datatables to be dynamic, they must always be server-side + ajax (as far as I know) so that there is no need to reload the page, otherwise you only need to use the page that comes with default laravel and you save work. Regarding the problem you have of speed depends on several factors:

  • Your team,
  • Your data,
  • The way programs,
  • The way you do your queries,
  • The amount of files that the framework php should read and your code,
  • Others ...

Well, I'll add my example. Just copy and paste.

SQL:

-- ----------------------------
-- Table structure for periodos_pagos
-- ----------------------------
DROP TABLE IF EXISTS 'periodos_pagos';
CREATE TABLE 'periodos_pagos' (
  'id_periodo_pago' int(10) unsigned NOT NULL AUTO_INCREMENT,
  'desc_periodo_pago' varchar(50) DEFAULT NULL,
  'fecha_creacion_periodo_pago' datetime DEFAULT NULL,
  'fecha_modificacion_periodo_pago' datetime DEFAULT NULL,
  'fecha_eliminacion_periodo_pago' datetime DEFAULT NULL,
  PRIMARY KEY ('id_periodo_pago')
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of periodos_pagos
-- ----------------------------
INSERT INTO 'periodos_pagos' VALUES ('1', '2000', '2018-06-30 00:59:47', '2018-06-30 03:36:30', null);
INSERT INTO 'periodos_pagos' VALUES ('2', '2001', '2018-06-30 01:08:21', '2018-07-02 03:05:11', null);
INSERT INTO 'periodos_pagos' VALUES ('3', '2003', '2018-09-21 14:59:18', '2018-09-21 15:00:02', null);
INSERT INTO 'periodos_pagos' VALUES ('4', '2002', '2018-09-21 15:14:57', null, null);
INSERT INTO 'periodos_pagos' VALUES ('5', '2004', '2018-09-23 20:28:01', '2018-09-23 20:28:20', null);
INSERT INTO 'periodos_pagos' VALUES ('6', '2005', '2018-09-24 21:57:19', null, null);

MODEL:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class PeriodoPago extends Model
{ 
    protected $table = 'periodos_pagos'; 

    protected $primaryKey = 'id_periodo_pago';

    protected $fillable = ['desc_periodo_pago'];    

    protected $guarded = ['id_periodo_pago'];

    protected $dates = [
        'fecha_creacion_periodo_pago',
        'fecha_modificacion_periodo_pago',
        'fecha_eliminacion_periodo_pago'
        ];

    public $timestamps = false;  

}

DRIVER:

<?php

namespace App\Http\Controllers;

use App\PeriodoPago;

use Illuminate\Http\Request;

use App\Http\Requests;

use Illuminate\Support\Facades\Validator;

use DB;

class PeriodosPagosController extends Controller
{
    public function index()
    {
        return View('periodos-pagos.index');
    }

    public function obtenerPeriodosPagos(Request $request)
    {
        // datatable column index  => database column name
        $columns = array(
            0 => 'id_periodo_pago', 
            1 => 'desc_periodo_pago',
            2 => 'fecha_creacion_periodo_pago',
        );

        $obtener_periodos_pago = PeriodoPago::query();

        $obtener_periodos_pago->select(
            'periodos_pagos.id_periodo_pago',
            'periodos_pagos.desc_periodo_pago',
            'periodos_pagos.fecha_creacion_periodo_pago'
        )
        ->whereNotNull(
            'id_periodo_pago'
        );


       $total_periodos_pago = PeriodoPago::whereNotNull('id_periodo_pago')
            ->count('id_periodo_pago');

        $totalData = $total_periodos_pago;


       if(empty($request->input('search.value'))){

             $totalFiltered = $totalData;

        }else{
            $search = $request->input('search.value');

            $totalFiltered = PeriodoPago::where('id_periodo_pago','LIKE',"%{$search}%")
                               ->orWhere('desc_periodo_pago', 'LIKE',"%{$search}%")
                                ->whereNotNull('id_periodo_pago')
                                ->count(DB::raw('id_periodo_pago'));

           $obtener_periodos_pago->where('id_periodo_pago','LIKE',"%{$search}%")
                ->orWhere('desc_periodo_pago', 'LIKE',"%{$search}%");

        }

       if (empty($request->input('length'))) {
            $limit = 10;
        }else{
            $limit = $request->input('length');         
        }

        if (empty($request->input('start'))) {
            $start = 0;
        }else{
            $start = $request->input('start');
        }

        if (empty($request->input('order.0.column'))) {
            $order = 'id_periodo_pago';
        }else{
            $order = $columns[$request->input('order.0.column')];
        }

        if (empty($request->input('order.0.dir'))) {
            $dir = 'asc';
        }else{
            $dir = $request->input('order.0.dir');
        }        

        $obtener_periodos_pago->offset($start);
        $obtener_periodos_pago->limit($limit);
        $obtener_periodos_pago->orderBy($order, $dir);

        $periodos_pago = $obtener_periodos_pago->get();

        $data = array();

        if(!empty($periodos_pago))
        {
            foreach ($periodos_pago as $periodo_pago)
            {
                $nestedData['id_periodo_pago'] = $periodo_pago->id_periodo_pago;
                $nestedData['desc_periodo_pago'] = $periodo_pago->desc_periodo_pago;                
                $nestedData['fecha_creacion_periodo_pago'] = date('j M Y h:i a', strtotime($periodo_pago->fecha_creacion_periodo_pago));

                $data[] = $nestedData;
            }
        }

        $json_data = array(
            "draw"            => intval($request->input('draw')),  
            "recordsTotal"    => intval($totalData),  
            "recordsFiltered" => intval($totalFiltered), 
            "data"            => $data
        );

        return response()->json($json_data);

    }
}

VIEW:

    <div class="container">
        <h2>Periodos de pago</h2>
        <nav>
            <div class="nav nav-tabs" id="nav-tab" role="tablist">
                <a class="nav-item nav-link active" data-toggle="tab" role="tab" aria-selected="true">Listado de periodos</a>
            </div>
        </nav>
        <div class="tab-content" id="nav-tabContent">
            <div class="tab-pane fade show active" id="nav-home" role="tabpanel" aria-labelledby="nav-home-tab">
                <div class="table-responsive">
                    <table class="table table-striped table-hover" cellspacing="0" width="100%" id="listado_periodos_pago">
                        <thead>
                            <th>No.</th>
                            <th>Periodo (año)</th>
                            <th>Fecha de registro</th>
                        </thead>
                    </table>
                </div>
            </div>
        </div>
    </div>

<script type="text/javascript">
//generar tabla al cargar pagina
var table = $('#listado_periodos_pago').DataTable({
    'language': {
        'sProcessing': 'Procesando...',
        'sLengthMenu': 'Mostrar _MENU_ registros',
        'sZeroRecords': 'No se encontraron resultados',
        'sEmptyTable': 'Ningún dato disponible en esta tabla',
        'sInfo': 'Mostrando registros del _START_ al _END_ de un total de _TOTAL_',
        'sInfoEmpty': 'Mostrando registros del 0 al 0 de un total de 0 registros',
        'sInfoFiltered': '(filtrado de un total de _MAX_ registros)',
        'sInfoPostFix': '',
        'sSearch': 'Buscar:',
        'sUrl': '',
        'sInfoThousands': ',',
        'sLoadingRecords': 'Cargando...',
        'oPaginate': {
            'sFirst': 'Primero',
            'sLast': 'Último',
            'sNext': 'Siguiente',
            'sPrevious': 'Anterior'
        },
        'oAria': {
            'sSortAscending': ': Activar para ordenar la columna de manera ascendente',
            'sSortDescending': ': Activar para ordenar la columna de manera descendente'
        }
    },
    'processing': true,
    'serverSide': true,
    'ajax': {
        'url': '{{ route("periodos-pagos.obtenerPeriodosPagos") }}',
        'dataType': 'json',
        'type': 'get'
    },
    'columns': [
        {'data': 'id_periodo_pago'},        
        {'data': 'desc_periodo_pago'},
        {'data': 'fecha_creacion_periodo_pago'}
    ],
    'rowId': 'id_periodo_pago',
    'columnDefs': [
        {
            'targets': 0,
            'className': 'text-center'
        }
    ]
});       

ROUTES:

Route::get('periodos-de-pagos', [
    'as' => 'periodos-pagos.index',
    'uses' => '[email protected]'
    ]);

Route::get('periodos-de-pagos/obtener-periodos-pagos', [
    'as' => 'periodos-pagos.obtenerPeriodosPagos',
    'uses' => '[email protected]'
    ]);

Possibly you should import the datatables styles (css and js) in your view and ignore the ones that the package that you installed will come with.

    
answered by 28.09.2018 в 22:44