Datatables pagination server side

[digambarfurnspace]

Hi All,

Currently getting all data from DB and pagination is handled on client side. I know same issue raised previously https://github.com/silverbux/laravel-angular-admin/issues/80 but not with proper solution (I think). Would be thankful any anyone help me with detailed implementation details.

Thanks in advance.

[gioramies]

You need a promise

http://l-lin.github.io/angular-datatables/archives/#!/dataReloadWithPromise

[gioramies]

Here is how I do it

HTML: <table datatable="" class="table table-striped table-bordered" ng-if="vm.displayTable" dt-options="vm.dtOptions" dt-instance="vm.dtInstance" dt-columns="vm.dtColumns" width="100%" ></table>

JS:

        function getFormData($form) {
            var unindexed_array = $form.serializeArray();
            var indexed_array = {};

            $.map(unindexed_array, function(n, i){
                indexed_array[n['name']] = n['value'];
            });

            return indexed_array;
        }

        let createdRow = (row) => {
            $compile(angular.element(row).contents())($scope)
        }

        let actionsHtml = (data) => {
            return `
                <div class="btn-group">
                    <button type="button" class="btn btn-default">Opciones</button>
                    <button type="button" class="btn btn-default dropdown-toggle" data-toggle="dropdown" aria-expanded="true">
                        <span class="caret"></span>
                        <span class="sr-only">Toggle Dropdown</span>
                    </button>
                    <ul class="dropdown-menu" role="menu">
                        <li><a ui-sref="app.customeredit({customerId: ${data.id}})"> <i class="fa fa-edit"></i> Editar </a></li>
                        <li><a ng-click="vm.delete(${data.id})"> <i class="fa fa-trash-o"></i> Eliminar </a></li>
                    </ul>
                </div>`
        }

        let Customers = this.API.all('customers')

        this.dtOptions = DTOptionsBuilder.newOptions().withOption('ajax', function(data, fnCallback, settings) {
            var formData = getFormData($('form[name="customerForm"]'));
            formData['settings'] = data;
            formData['offset'] = new Date().getTimezoneOffset();
            Customers.customGET("customers", formData)
            .then(function(response) {
                //let dataSet = response.plain()
                fnCallback({
                    draw: response.data.draw,
                    recordsTotal: response.data.recordsTotal,
                    recordsFiltered: response.data.recordsFiltered,
                    data: response.data.customers
                })
            })
        }).withPaginationType('full_numbers')
            .withDataProp('data')
            .withOption('processing', true)
            .withOption('serverSide', true)
            .withOption('createdRow', createdRow)
            .withOption('responsive', true)
            .withBootstrap()
            .withBootstrapOptions({
                TableTools: {
                    classes: {
                        container: 'btn-group',
                        buttons: {
                            normal: 'btn btn-danger'
                        }
                    }
                },
                pagination: {
                    classes: {
                        ul: 'pagination pagination-sm'
                    }
                }
            })
        this.dtColumns = [
            DTColumnBuilder.newColumn('id').withTitle('ID').withClass('all'),
            DTColumnBuilder.newColumn('name').withTitle('Nombre').withClass('all'),
            DTColumnBuilder.newColumn('identification').withTitle('Identificación'),
            DTColumnBuilder.newColumn('assigned.name').withTitle('Responsable'),
            DTColumnBuilder.newColumn(null).withTitle('Acciones').notSortable().renderWith(actionsHtml).withClass('all')
        ];

        this.reloadData = reloadData;
        this.dtInstance = {};

        this.displayTable = true   

        function reloadData() {
            var resetPaging = true;
            this.dtInstance.reloadData(callback, resetPaging);
        }
        function callback(json) {  
            console.log(json);
        }

PHP:

public function getCustomers()
    {
        $date_start = Input::get('date_start');
        $date_end = Input::get('date_end');
        $customer_type_id = Input::get('customer_type_id');
        $customer_industry_id = Input::get('customer_industry_id');
        $assigned_user_id = Input::get('assigned_user_id');

        $customers = Customer::with('type', 'industry', 'gender', 'creator', 'assigned', 'modifier');

        $recordsTotal = Customer::all()->count();

        if( !empty($date_start) ) {
            $customers->whereDate('customers.created_at', '>=', $date_start);
        }
        if( !empty($date_end) ) {
            $customers->whereDate('customers.created_at', '<=', $date_end);
        }
        if( !empty($customer_type_id) ) {
            $customers->where('customers.customer_type_id', '=', $customer_type_id);
        }
        if( !empty($customer_industry_id) ) {
            $customers->where('customers.customer_industry_id', '=', $customer_industry_id);
        }
        if( !empty($assigned_user_id) ) {
            $customers->where('customers.assigned_user_id', '=', $assigned_user_id);
        }

        $settings = json_decode(Input::get('settings'), TRUE);

        $draw = $settings['draw'];
        $length = $settings['length'];
        $start = $settings['start'];
        $search = $settings['search']['value'];
        $orderCol = $settings['order'][0]['column'];
        $orderDir = $settings['order'][0]['dir'];
        $columns = $settings['columns'];

        $columnNames = [
            'id' => 'id',
            'name' => 'name',
            'identification' => 'identification',
            'email' => 'email',            
            'phone_mobile' => 'phone_mobile',
            'home_address_city' => 'home_address_city',
            'type.name' => 'customer_type_id',
            'assigned.name' => 'assigned_user_id'
        ];

        if( !empty(trim($search)) ) {
            $customers->where(DB::raw("CONCAT(IFNULL(customers.name, ''), ' ', IFNULL(customers.identification, ''), ' ', IFNULL(customers.email, ''), ' ', IFNULL(customers.phone_mobile, ''), ' ', IFNULL(customers.phone_fax, ''), ' ', IFNULL(customers.phone_home, ''), ' ', IFNULL(customers.phone_work, ''), ' ', IFNULL(customers.home_address_country, ''), ' ', IFNULL(customers.work_address_country, ''), ' ', IFNULL(customers.home_address_state, ''), ' ', IFNULL(customers.work_address_state, ''), ' ', IFNULL(customers.home_address_city, ''), ' ', IFNULL(customers.work_address_city, ''), ' ', IFNULL(customers.account_name, ''), ' ', IFNULL(customers.referred_by, ''))"), 'LIKE', "%".$search."%");
        }
        if( !empty($orderCol) && isset($columns[$orderCol]) ) {
            $customers->orderBy($columnNames[$columns[$orderCol]['data']], $orderDir);
        }

        $recordsFiltered = $customers->count();

        if( intval($start) > 0 ) {
            $customers->skip($start);
        }
        if( intval($length) > 0 ) {
            $customers->take($length);
        }

        $customers = $customers->get();

        $customers = $customers->toArray();

        $response['customers'] = $customers;
        $response['draw'] = $settings['draw'];
        $response['recordsTotal'] = $recordsTotal;
        $response['recordsFiltered'] = $recordsFiltered;

        return response()->success($response);
    }