Datatables Editor and Server Side

User123456 · August 2017

Now that I finished using datatables editor leftjoin(), I need to know, how to implement server side activation with datatables editor? What are the necessary commands to achieve this?

Here is my code:

Join.html

<script type="text/javascript" language="javascript" class="init">
        var editor;

        $(document).ready(function() {
            editor = new $.fn.dataTable.Editor( {
                ajax: "../php/join.php",
                table: "#example",
                fields: [ {
                    label: "Publicação:",
                    name: "t.PublicationTitle"
                }, {
                    label: "Ano:",
                    name: "p.ano"
                }, {
                    label: "Competência:",
                    name: "p.competencia"
                }, {
                    label: "Empresa:",
                    name: "c.razaoSocial",
                }, {
                    label: "Empregado:",
                    name: "e.nome"
                }
                ]
            });

            $('#example').DataTable( {
                dom: "Bfrtip",
                ajax: {
                    url: "../php/join.php",
                    type: 'POST'
                },
                columns: [
                { data: "t.PublicationTitle" },
                { data: "p.ano" },
                { data: "p.competencia" },
                { data: "c.razaoSocial" },
                { data: "e.nome"},
                ],
            } );
        } );
    </script>

Join.php

<?php
include( "../../php/DataTables.php" );
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

Editor::inst( $db, 'tbl_publication AS p', 'id_Publication' )
    ->fields(
        Field::inst( 't.PublicationTitle' ),
        Field::inst( 'p.ano' ),
        Field::inst( 'p.competencia' ),
        Field::inst( 'c.razaoSocial' ),
        Field::inst( 'e.nome')
    )
    ->leftJoin( 'tbl_ptitle AS t', 'p.fk_titulo', '=', 't.id_PublicationTitle' )
    ->leftJoin( 'tbl_company AS c', 'p.fk_empresa', '=', 'c.id_Company' )
    ->leftJoin( 'tbl_employee AS e', 'p.fk_empregado', '=', 'e.id_Employee' )
    ->process($_POST)
    ->json();

rf1234 · August 2017

https://datatables.net/reference/option/serverSide

and here is a very simple example from my coding. This table has over 300,000 rows. I had issues with server side when I used the ajax default type which is GET. Using POST was better - and you already have that in your table definition.

var rateTable = $('#tblRate').DataTable( {
        dom: "Bfrltip",
        processing: true,
        serverSide: true,    //server side only works well with type "POST" !!!
        ajax: {
            url: 'actions.php?action=tblRate',
            type: 'POST'
        },
        pageLength: 20,
        lengthMenu: [5, 10, 20, 50, 100, 200, 500],
        columns: [
            {   data: "rate.ref_rate" },
            {   data: "rate.currency" },
            {   data: "rate.ref_rate_period" },
            {   data: "rate.date" },
            {   data: "rate.rate" },
            {   data: "rate.update_time" }
        ],
        order: [[ 3, 'desc' ]],
        select: {
            style: 'single'
        },            
        buttons: [
            {   extend: "create", editor: rateEditor },
            {   extend: "edit",   editor: rateEditor },
            {   extend: "remove", editor: rateEditor },
                        "colvis"
        ]
    } );

on the back end side there's really nothing you need to do:

Editor::inst( $db, 'rate' )
    ->field(
        Field::inst( 'rate.ref_rate' ),
        Field::inst( 'rate.currency' ),
        Field::inst( 'rate.ref_rate_period' ),
        Field::inst( 'rate.date' )
            ->getFormatter( function ( $val, $data, $opts ) {
                    return getFormatterDate($val);                     
                } )
            ->setFormatter( function ( $val, $data, $opts ) {
                    return setFormatterDate($val);
                } ),
        Field::inst( 'rate.rate' )
            ->validator( function ( $val, $data, $opts ) {
                return validatorRate($data['rate'], $val);
            } )
            ->getFormatter( function($val, $data, $opts) {
                return getFormatterRate($val);
            })
            ->setFormatter( function($val, $data, $opts) {
                return setFormatterRate($val);
            }),
        Field::inst( 'rate.update_time' )
    )
    ->process($_POST)
    ->json();

allan · August 2017

Have a look at this example as well.

The key is to enable serverSide and have DataTables send a POST request (it sends GET by default).

Allan

Datatables Editor and Server Side

Datatables Editor and Server Side

Answers

Howdy, Stranger!

Categories

DataTables

Datatables Editor and Server Side

Datatables Editor and Server Side

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables