Datatables Editor and Server Side

User123456

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

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

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