Using Join & Subqueries With Editor

notyou61

Hello,
I am using DataTables Editor to display a list of employees from the employee’s database table along with a column providing their last action in the actions table. Currently I am using the employee’s primary key to call a function which provides the last action data however because it is called for each record it adds the load time to the datagrid. Please see the current code below:

Javascript

            // Employee Datagrid
            employeesTable = $('#tblEmployees').DataTable({
                 Grid Options
                dom: "Tfrtip",
                lengthChange: false,
                bAutoWidth: false,
                jQueryUI: true,
                bProcessing: false,
                iDisplayLength: 10,
                sPaginationType: "full_numbers",
                ordering: true,
                order: [ 0, 'asc' ],
                bPaginate: true,
                bDestroy: true,
                bFilter: true,
                bSearch: true,
                bInfo: true,
                bServerSide: false,
                bRetrieve: true,
                 Ajax Call
                ajax: {
                    url: 'queryPage.php?',
                    data: 'varGridTest=y',
                    type: 'GET',
                    dataType: "json",
                    contentType: "application/json; charset=utf-8",
                    async: false,
                    error: function(xhr, strError) {strError},
                    success: function(data) {
                        // Return
                        functionReturn = text;
                    }
                },
                 Ajax
                ajax: {
                     URL
                    url: "queryPage.php",
                     Ajax Parameters
                    data: {
                            varGridTest:1
                          },
                     Ajax Type
                    type: 'GET',
                     Data Type
                    dataType: 'json'
                },
                 Columns
                columns: [
                     Employee User ID
                    { 
                        data: "actionUserID",
                        visible: false,
                    },
                    // Employee Name
                    { 
                        title: "Employee",
                        data: null, render: function (data, type, row) 
                            {
                                // Combine the first and last names into a single table field
                                return  '<a href="#" id=lnkInstantMessage onClick="createDesktopInstantMessage(' + data.tblUsers.userID + ')" class="glyphicon glyphicon-bullhorn" style="text-decoration: none;">&nbsp;</a>' +
                                        '<a href="#" id=lnkModal1 class="modalClass" data-id=' + data.tblUsers.userID  + ' title="Click here to view/edit employee details">' +  data.tblUsers.userHonorific + ' ' + data.tblUsers.userFirstName + ' ' + data.tblUsers.userLastName + ', ' + data.tblUserTitles.userTitle + '</a>';
                            }, 
                        width: "45%" 
                    },
                    // Employee Last Action
                    { 
                        title: "Last Action",
                        data: null, 
                        render: function (data, type, row) 
                            {
                                // Format User Action
                                return obtainUserLastAction(data.tblUsers.userID); // Function used to provide the last action details. 
                            }, 
                        width: "55%" 
                    }
                ],
                 Grid Buttons
                tableTools: {
                    sRowSelect: "os",
                    aButtons: []
                }
            }); 

PHP

    // Database Fields
    $editor = Editor::inst( $db, 'tblUsers', 'userID' )
        ->fields(
            Field::inst('tblUsers.userID'),
            Field::inst('tblUsers.userHonorific'),
            Field::inst('tblUsers.userFirstName'),
            Field::inst('tblUsers.userLastName'),
            Field::inst('tblUsers.usersTitleID'),
            Field::inst('tblUsers.userBirthday'),
            Field::inst('tblUsers.userSince'),
            Field::inst('tblUsers.userNotVaildDate'),
            Field::inst('tblUserTitles.userTitle')
        );
    // Where Clause (Filter For Employees)
    $editor
        ->where(function($whereClause) {
            // Filters for employees
            $whereClause->where('tblUserTitles.isEmployeeTitle', '1');
        });
    // Process Json
    $editor
        // Join Query
        ->leftJoin('tblUserTitles', 'tblUserTitles.userTitleID', '=', 'tblUsers.usersTitleID')
        ->process($_POST)
        ->json();

What I would like to do is to join this tables together and use a subquery in place of the function in an effort to make the grid load faster. I have the created the following PHP code which provides the desired results.

    $querySelect    = "tableAliase.actionUserID, tableAliase.actionTime, tblActionTypes.actionTypeName, " .
                      "tblUsers.userID, tblUsers.userHonorific, tblUsers.userFirstName, tblUsers.userLastName, tblUserTitles.userTitle"; 
    $tableName      = "tblActions tableAliase";
    $joinClause     = "(SELECT `actionUserID`, MAX(`actionID`) AS MaxActionID FROM tblActions GROUP BY `actionUserID`) groupedTableAliase";
    $onClause       = "tableAliase.actionUserID = groupedTableAliase.actionUserID";
    $andClause      = "tableAliase.actionID = groupedTableAliase.MaxActionID";
    $joinClause1    = "tblActionTypes";
    $onClause1      = "tblActionTypes.actionTypeID = tableAliase.actionTaken";
    $joinClause2    = "tblUsers";
    $onClause2      = "tblUsers.userID = tableAliase.actionUserID";
    $joinClause3    = "tblUserTitles";
    $onClause3      = "tblUserTitles.userTitleID = tblUsers.usersTitleID";
    $orderBy        = "tableAliase.actionUserID";
    $query          = "SELECT $querySelect FROM $tableName " .
                      "INNER JOIN $joinClause ON $onClause " .
                      "JOIN $joinClause1 ON $onClause1 " .
                      "JOIN $joinClause2 ON $onClause2 " .
                      "JOIN $joinClause3 ON $onClause3 " .
                      "AND $andClause " .
                      "ORDER BY $orderBy ASC"; // query

After hours of research I have been unable to determine how to properly set this up with the Editor. Any help would be much appreciated!

allan

Hi,

Unfortunately, at this time, it is not possible to use such a query with Editor. Sub-selects are not supported.

However, what you could do, is just use your SQL query directly as you have above to populate the table's data, and then use the Editor class in order to perform the create, edit and delete actions. This assumes that you will only be creating data on the main table and not the joined ones though.

Allan

notyou61

Thanks Allan