Page load slow - Large linked table.

Erik Skov

I have table1 (rr_adjuster_supervisor_join) with a linked column - id of table2 (rr_adjusters), where table 2 is 35K rows. It is taking a long time to render the page. I have attempted to add a name field and an editor.dependent() to filter the data. The filter appears to be working, but does not solve the speed problem. Is there a way I could identify and skip a piece of the code in the loading of the page and only load that when the edit button is clicked? Suggestions appreciated.

I know if I remove the - type: select - line from the code below, the page loads much faster.

                { "label": "Adjuster:",
                    "name": "rr_adjuster_supervisor_join.adjuster_id",
                    "type": "select",
                     placeholder: "Select Adjuster"
                }

php

$incSupervisorId = filter_input(INPUT_POST, 'incSupervisorId', FILTER_SANITIZE_NUMBER_INT); // $_POST['incSupervisorId'];

    // Build our Editor instance and process the data coming from _POST
Editor::inst($db, 'rr_adjuster_supervisor_join', 'id')
    ->fields(
        Field::inst('rr_adjuster_supervisor_join.id')->set(false),

        Field::inst('rr_adjuster_supervisor_join.supervisor_id')
            ->options(Options::inst()
                ->table('rr_adjuster_supervisor, users')
                ->value('rr_adjuster_supervisor.id')
                ->label( array ('users.last_name', 'users.first_name'))
                ->render(function ( $row ) {
                    return  $row['users.last_name'].', '.$row['users.first_name'];
                })
                ->where( function ( $q ) {
                    $q ->where( function($r) {
                        global $incSupervisorId;
                        if($incSupervisorId > -1){
                            $r->where ('rr_adjuster_supervisor.id', $incSupervisorId);
                        }
                        // this is the actual inner join of the tables.
                        // You need the "false" in order to avoid "table.id" being escaped as a string.
                        $r ->where('rr_adjuster_supervisor.user_id', 'users.id', '=', false);
                        $r ->where('users.disabled', 'N', '=');
                    });
                })
            )
            ->validator('Validate::dbValues'),

        Field::inst('rr_adjuster_supervisor_join.adjuster_id')
            ->options(Options::inst()
                ->table('rr_adjusters')
                ->value('id')
                ->label( ['last_name', 'first_name', 'phone'] )
                ->render(function ( $row ) {
                    return  $row['last_name'].', '.$row['first_name'].' - '.$row['phone'] ;
                })
            )
            ->validator('Validate::dbValues'),

        Field::inst('rr_adjusters.last_name as aj_last_name'),
        Field::inst('rr_adjusters.first_name as aj_first_name'),
        Field::inst('rr_adjusters.phone as aj_phone'),
        Field::inst('rr_adjusters.extension as aj_ext'),
        Field::inst('rr_adjusters.fax as aj_fax'),
        Field::inst('rr_adjusters.email as aj_email'),
        Field::inst('rr_adjusters.active as aj_active'),
        Field::inst('users.last_name as sv_last_name'),
        Field::inst('users.first_name as sv_first_name')
    )

    ->leftJoin('rr_adjuster_supervisor', 'rr_adjuster_supervisor.id', '=', 'rr_adjuster_supervisor_join.supervisor_id')
    ->leftJoin('rr_adjusters', 'rr_adjusters.id', '=', 'rr_adjuster_supervisor_join.adjuster_id')
    ->leftJoin('users', 'users.id', '=', 'rr_adjuster_supervisor.user_id')

    ->where( function ( $q ) {
        global $incSupervisorId;
        if($incSupervisorId > -1){
            $q->where('rr_adjuster_supervisor.id', $incSupervisorId);
        }
    })

js

(function ($) {

    $(document).ready(function () {

        // prevents datatables default obtrusive developer alert message and allows table specific error handling
        // NOTE: this means errors from DT Editor will NOT be displayed - must handle errors manually
        $.fn.dataTable.ext.errMode = 'none';

        var editor = new $.fn.dataTable.Editor({
            ajax: {
                url: './table.supervisedAdjusters.php',
                type: "POST",
                data: function ( d ) {
                    d.incSupervisorId = incSupervisorId
                    , d.mailChangesTo = mailChangesTo;
                    ;
                }
            },
            table: '#supervisedAdjusters',
            fields: [
                { "label": "Supervisor:",
                    "name": "rr_adjuster_supervisor_join.supervisor_id",
                    "type": "select",
                    "def": ( incSupervisorId > -1 ? incSupervisorId : ""),
                    placeholder: "Select Supervisor"
                },

                { "label": "Adjuster:",
                    "name": "rr_adjuster_supervisor_join.adjuster_id",
                    "type": "select",
                     placeholder: "Select Adjuster"
                }
            ]
        });

        editor.add( {
            label: "Adjuster Last Name Contains:",
            name: "name_contains"
        }, "rr_adjuster_supervisor_join.supervisor_id");

        //editor.dependent("name_contains", 'relative path and .php file');
        editor.dependent("name_contains", function(val, data, callback) {

            var nameVal = "";
            if (typeof document.getElementById('DTE_Field_name_contains') != "undefined"){
                var nameObj = document.getElementById('DTE_Field_name_contains');
                if(nameObj != null){
                    nameVal = nameObj.value;
                }
            }

            $.ajax({
                type: "POST",
                url: '../../adjusters/filter.adjusters.php',
                data: {
                    dte_table: "rr_adjuster_supervisor_join",
                    contains: nameVal
                },
                dataType: 'json',
                success: function (json) {
                     callback(json);
                }
            });
        });

        // display technical error message
        editor.on("submitError", function (e, xhr, err, thrown, data) {
            editor.error("An error has occurred, " + err + ": " + xhr.responseText);
        });

        var table = $('#supervisedAdjusters').DataTable({
            ajax: {
                url: './table.supervisedAdjusters.php',
                type: "POST",
                data: function ( d ) {
                    d.incSupervisorId = incSupervisorId;
                }
            },
            serverSide: true,
            lengthMenu: [
                [ 10, 25, 50, -1 ],
                [ '10 rows', '25 rows', '50 rows', 'Show all (Slow!!)' ]
                ],
            // "dom": 'Blfrtip',
            columns: [
                { "data": "aj_last_name"}
                , { "data": "aj_first_name"}
                , { "data": "aj_phone"}
                , { "data": "aj_ext"}
                , { "data": "aj_fax"}
                , { "data": "aj_email"}
                , { "data": "aj_active"}

                , { "data": "rr_adjuster_supervisor_join.id"}
            ],
            select: 'single'/*,
            lengthChange: false*/
        });

        table.on('error', function (e, settings, techNote, message) {
            $("#loginErrorDialog").dialog("open");
            console.log(techNote);
            console.log(message);
        });

        new $.fn.dataTable.Buttons(table, [

            { extend: 'collection',
                text: 'Export',
                buttons: [
                    'copy',
                    'excel',
                    'csv',
                    'pdf',
                    'print'
                ]
            }
        ]);

        var defaultInsert = 1;  // would like this to be a count of buttons so far.

        if(hasEdit){

            // add the create button.
            table.button().add( defaultInsert
                , {extend: "create"
                , editor: editor});

            // add the edit button.
            table.button().add( defaultInsert
                , {extend: "edit"
                , editor: editor});

        }

        table.buttons().container()
                .prependTo($('div.fg-toolbar:eq(0)', table.table().container()));
    });

}(jQuery));

filter php file

$contains = filter_input(INPUT_POST, 'contains'); // $_POST['contains'];

$tableName = filter_input(INPUT_POST, 'dte_table'); // $_POST['dte_table'];

// listener
// get companies for a given parent
$res = $db
    ->query('select')
        ->table( 'rr_adjusters')
        ->get('id as value', 'CONCAT(last_name," - ",first_name) as label')
        ->where('last_name', '%'.$contains.'%', 'LIKE')
        ->exec();

$adjusters = $res->fetchAll();

// sort the results
$keys = array_map(function($val) { return $val['label']; }, $adjusters);
array_multisort($keys, $adjusters);

echo json_encode( [
    'options' => [
        // variable table name allows reuse of this php file.
        $tableName.".adjuster_id" => $adjusters
    ]
] );

allan

Hi,

Just to confirm, it is the load of options from rr_adjusters that is causing the long load times? You could drop the Options class from that field and it would just load without the options predefined.

Then reply upon your dependent() code to populate the list when the form is used.

Regards,
Allan

Erik Skov

Thank you. I think you are on to something. Commenting out the options makes the page load fantastically. I just need to refine the dependent() because the NEW / EDIT page is super slow, which must be for the reason that when it opens, the value for the field is empty.

I will update again when I have more information based on testing.

Erik Skov

Populating the list. null values.

// get adjuster for a given last name first 5
$res = $db
->query('select')
->table( 'rr_adjusters')
->get('id as value, CONCAT( last_name, ", " , first_name, " - ", phone, " - ", email) as label')
->where('last_name', $contains.'%', 'LIKE')
->exec();

$adjusters = $res->fetchAll();

The above works great. Except if one of the values, like the email, is null. In that case the entire row returns as null.

The sql that I would put there is:
// ->get('id as value, CONCAT( last_name, ", " , first_name, " - ", IFNULL(phone, "no phone"), " - ", IFNULL(email, "no email")) as label')

Taken from this, which in mysql runs just fine.

SELECT id as value, CONCAT( last_name, ", " , first_name, " - ", IFNULL(phone, "no phone"), " - ", IFNULL(email, "no email")) as label
FROM rr_adjusters
WHERE last_name LIKE 'water%'

But the interpreter causes this line to generate the following sql statement, according to my sql log:

SELECT id as 'value', CONCAT( last_name, " as '"', " as '"', first_name as 'first_name', " - " as '" - "', IFNULL(phone, "no phone"), " - " as '" - "', IFNULL(email, "no email")) as label FROM rr_adjusters WHERE last_name LIKE 'water%'

Which suggests the IFNULL inside the CONCAT is causing an issue.

Do I need to code the sql query in a custom / different way?

Erik Skov

I found this:
https://datatables.net/forums/discussion/49461
which led me to use this:

// listener
// get adjuster for a given last name first 5
$res = $db
    ->sql('SELECT `id` as value, CONCAT( `last_name`, ", " , `first_name`'
            . ', " - ", IFNULL(`phone`, "no phone")'
            . ', " - ", IFNULL(`email`, "no email")) as label'
            . ' FROM  `rr_adjusters`'
            . ' WHERE `last_name` LIKE "water%"');

$adjusters = $res->fetchAll();

which is working.

Erik Skov

That last line of the query should read:
' WHERE last_name LIKE "'.$contains.'%"'

for completeness, I also ended up adding this to the js file:

        editor.on('initEdit', function(e, node, data, items, type) {
            console.log("init Edit: "+data.aj_last_name);
            editor.field( "name_contains" ).set( data.aj_last_name );
        });

This is getting so close, so good!

Erik Skov

This project is now working well. Thanks for the help.

allan

Awesome - great to hear you've got it working. Thanks for the update.

Allan