Using the Join Method without updating the Data in the Joined table

jdupaul

Hello,

I was hoping someone could assist me with an issue I am having with Editor DataTables. I am trying to utilize the LeftJoin() method when querying on two tables and I want my code to display information from both tables but only update the main table when updating a record and not the joined table.

Below is my PHP code

/*
 * Example PHP implementation used for the index.html example
 */
// DataTables PHP library
include( "edit-php/DataTables.php" );
 
// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;
 
// Build our Editor instance and process the data coming from _POST

Editor::inst( $db, 'TABLE1', 
    array(
        'TABLE1.ID_NBR',
        'TABLE1.ACTION_DATE',
        'TABLE1.ACTION_NAME',
        'TABLE1.COMMENTS',
        'TABLE1.BADGE',
        'TABLE1.ACTION_ID',
        'TABLE1.ACTION_INSTANCE',
        'TABLE1.TIME_FLAG')
    )
    ->fields(
        Field::inst( 'TABLE1.ID_NBR' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::maxLen', 10 )
            ->getFormatter( 'Format::CapitaliseStringValues' )
            ->setFormatter( 'Format::CapitaliseStringValues' ),
        Field::inst( 'TABLE1.ACTION_DATE' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
            ) )
            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
            ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),      
        Field::inst( 'TABLE1.ACTION_NAME' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::maxLen', 30 )
            ->getFormatter( 'Format::CapitaliseStringValues' )
            ->setFormatter( 'Format::CapitaliseStringValues' ),
        Field::inst( 'TABLE1.COMMENTS' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::maxLen', 200 ),
        Field::inst( 'TABLE1.BADGE' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::maxLen', 6 )
            ->validator( 'Validate::minLen', 6 )
            ->validator( 'Validate::numeric' ),
        Field::inst( 'TABLE1.ACTION_ID' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::maxLen', 30 )
            ->getFormatter( 'Format::CapitaliseStringValues' )
            ->setFormatter( 'Format::CapitaliseStringValues' ),
        Field::inst( 'TABLE1.ACTION_INSTANCE' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::maxLen', 2 )
            ->validator( 'Validate::minLen', 1 )
            ->validator( 'Validate::numeric' ),
        Field::inst( 'TABLE1.TIME_FLAG' )
            ->validator( 'Validate::notEmpty' )
            ->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
            ) )
            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
            ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
        Field::inst( 'TABLE2.CLASS' )
    )
    ->leftJoin( 'TABLE2', 'TABLE2.ID_NBR', '=', 'TABLE1.ID_NBR' )
    ->process( $_POST )
    ->json();

Basically, I want to display the 'TABLE2.CLASS' field but I don't want it to update 'TABLE2.CLASS' when I edit a record.

Is there any way to do this?

rf1234

https://editor.datatables.net/manual/php/getting-started#SQL-functions

->set( false )

allan

Also don't have a TABLE2.CLASS field defined in your client-side initilisation of Editor.

Allan

jdupaul

@rf1234 - Awesome! Thank you for pointing me in the right direction. I completely overlooked this piece. >_<

@allan - Ah okay, thank you for the information and recommendation. I'll make sure to change this going forward.

jdupaul

Hello again, it seems like I am still having issues.
- I have updated "TABLE2.CLASS" to "table2.atis_class" in my oracle datatable
- I have placed ->set( false) in my php code under "table.atis_class"

My table is still not properly updating rows when I attempt edit, insert, delete, etc from the table. It doesn't throw any form of error either, it just doesn't perform the action.

Below is all of my code.

HTML:

            <html>
                <body>

                    <!------------------------ CSS ---------------------------------->
                    <link rel="stylesheet" type="text/css" href="/homepages/departments/436/Information_Services/includes/jquery_plugins/DataTables/css/jquery.dataTables.min.css">
                    <link rel="stylesheet" type="text/css" href="/homepages/departments/436/Information_Services/includes/jquery_plugins/Buttons/css/buttons.dataTables.min.css">
                    <link rel="stylesheet" type="text/css" href="/homepages/departments/436/Information_Services/includes/jquery_plugins/Select/css/select.dataTables.min.css">
                    <link rel="stylesheet" type="text/css" href="/homepages/departments/436/Information_Services/includes/jquery_plugins/Editor/css/editor.dataTables.min.css">
                    <link rel="stylesheet" type="text/css" href="/homepages/departments/436/Information_Services/includes/jquery_plugins/FixedColumns/css/fixedColumns.dataTables.min.css">

                    <!------------------------ JAVASCRIPT ---------------------------------->
                    <script type="text/javascript" language="javascript" src="/homepages/departments/436/Information_Services/includes/jquery_plugins/DataTables/js/jquery.dataTables.min.js"></script>
                    <script type="text/javascript" language="javascript" src="/homepages/departments/436/Information_Services/includes/jquery_plugins/Editor/js/dataTables.editor.min.js"></script>
                    <script type="text/javascript" language="javascript" src="/homepages/departments/436/Information_Services/includes/jquery_plugins/Buttons/js/dataTables.buttons.min.js"></script>
                    <script type="text/javascript" language="javascript" src="/homepages/departments/436/Information_Services/includes/jquery_plugins/Select/js/dataTables.select.min.js"></script>
                    <script type="text/javascript" language="javascript" src="/homepages/departments/436/Information_Services/includes/jquery_plugins/Buttons/js/buttons.print.min.js"></script>
                    <script type="text/javascript" language="javascript" src="/homepages/departments/436/Information_Services/includes/jquery_plugins/FixedColumns/js/dataTables.fixedColumns.min.js"></script>
                    <script type="text/javascript" language="javascript" src="/homepages/departments/436/Information_Services/includes/javascript/dataTables.pageResize.min.js"></script>
                    <script type="text/javascript" language="javascript" src="/homepages/departments/436/Information_Services/includes/jquery_plugins/Buttons/js/buttons.print.min.js"></script>
                    <script type="text/javascript" language="javascript" src="/homepages/departments/436/Information_Services/includes/jquery_plugins/Buttons/js/buttons.colVis.min.js"></script>
                    <script type="text/javascript" src="/homepages/departments/436/Information_Services/includes/plugins/jQuery.print-master/jQuery.print.js"></script> 
                    <script type="text/javascript" language="javascript" src="/homepages/departments/436/Information_Services/includes/jquery_plugins/Editor/js/editor.display.js"></script>
                    <script type="text/javascript" src="/homepages/departments/436/Information_Services/includes/javascript/D436SdiAdminActions_Scripts.js"></script>
                    <script type="text/javascript" src="/jquery/jquery_plugins/jquery_datatables_latest/extras/FixedHeader/js/FixedHeader.min.js"></script>

                <div class="content centered" id="content">
                        <section>                       
                        <div id="d436_sdi_adminactions_div">
                            <table id="d436_sdi_adminactions" class="ui celled table" cellspacing="0" width="100%">
                            <h2 class="centered">Department 436 SDI Actions Tables</h2><br>
                                <thead>
                                    <tr>
                                    <th></th>
                                    <th>id_nbr</th> 
                                    <th>atis_class</th>
                                    <th>action_date</th> 
                                    <th>action_name</th> 
                                    <th>comments</th> 
                                    <th>badge</th> 
                                    <th>action_id</th> 
                                    <th>action_instance</th>
                                    <th>time_flag</th>                  
                                    </tr>
                                    <tr id="d436_sdi_adminactions_filter">
                                    <td></td>
                                    <td>id_nbr</td> 
                                    <td>atis_class</td>
                                    <td>action_date</td> 
                                    <td>action_name</td> 
                                    <td>comments</td> 
                                    <td>badge</td> 
                                    <td>action_id</td> 
                                    <td>action_instance</td>
                                    <td>time_flag</td>
                                    </tr>
                                </thead>
                            </table>
                            <div style="display: none;" id="d436_sdiactions_customForm_div">
                                <div id="d436_sdiactions_customForm">
                                    <fieldset class="report_info">
                                        <legend>Primary Key(s)</legend>
                                        <editor-field name="table1.id_nbr"></editor-field>
                                    </fieldset>
                                    <fieldset class="report_filters">
                                        <legend>Report Filters</legend>
                                        <editor-field name="table1.action_date"></editor-field>
                                        <editor-field name="table1.action_name"></editor-field>
                                        <editor-field name="table1.badge"></editor-field>       
                                        <editor-field name="table1.action_id"></editor-field>               
                                        <editor-field name="table1.action_instance"></editor-field>
                                        <editor-field name="table1.time_flag"></editor-field>
                                    </fieldset>
                                    <fieldset class="report_details">
                                        <legend>Report Details</legend>
                                        <editor-field name="table1.comments"></editor-field>
                                    </fieldset>
                                </div>
                            </div>
                        </div>
                        </section>
                    </div>          
                </body>
            </html>

jdupaul

JAVASCRIPT:

                    var d436sdiadminactions_editor; // use a global for the submit and return data rendering in the examples

                    $(document).ready(function() {
                        d436sdiadminactions_editor = new $.fn.dataTable.Editor( {
                            ajax: "/php/ebsw/ebis/ajax_d436sdiadminactions.php",
                            table: "#d436_sdi_adminactions",
                            template: '#d436_sdiactions_customForm',
                            fields: [ {
                                    label: "id_nbr:",
                                    name: "table1.id_nbr",
                                    type: 'text'
                                }, 
                                {
                                    label: "atis_class:",
                                    name: "table2.atis_class",
                                    type: "display"
                                },
                                {
                                    label: "action_date:",
                                    name: "table1.action_date",
                                    type:  'datetime'
                                },{
                                    label: "action_name:",
                                    name: "table1.action_name",
                                    type: 'text'
                                }, {
                                    label: "comments:",
                                    name: "table1.comments",
                                    type: 'textarea'
                                }, {
                                    label: "badge:",
                                    name: "table1.badge",
                                    type:  'text'
                                }, {
                                    label: "action_id:",
                                    name: "table1.action_id",
                                    type:  'text'
                                }, {
                                    label: "action_instance:",
                                    name: "table1.action_instance",
                                    type:  'text'
                                }, {
                                    label: "time_flag:",
                                    name: "table1.time_flag",
                                    type:  'datetime'
                                }

                            ]
                        } );

                        d436sdiadminactions_editor.on( 'onInitEdit', function () {
                            d436sdiadminactions_editor.disable('table1.id_nbr');
                            d436sdiadminactions_editor.disable('table2.atis_class');
                            d436sdiadminactions_editor.disable('table1.action_name');
                            d436sdiadminactions_editor.disable('table1.action_id');
                        } ).on('open', function (){
                            d436sdiadminactions_editor.disable('table1.id_nbr');
                            d436sdiadminactions_editor.disable('table2.atis_class');
                            d436sdiadminactions_editor.disable('table1.action_name');
                            d436sdiadminactions_editor.disable('table1.action_id');
                        }); 

                        $('#d436_sdi_adminactions thead tr#d436_sdi_adminactions_filter td').each( function () {
                            var title = $('#d436_sdi_adminactions thead tr#d436_sdi_adminactions_filter td').eq( $(this).index() ).text();
                            if($(this).index() > 0){
                                $(this).html( '<input type="text" placeholder="Search '+title+'" /><span class="invisible">'+title+'</span>' );
                            }
                        } );

                        var hideFromExport = [0];
                        var d436_edit_sdi_adminactions = $('#d436_sdi_adminactions').DataTable( {
                            "sDom": '<"top"lBpf>rt<"bottom"i><"top"p><"clear">',
                            "lengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
                            "pageLength": 10,
                            "scrollY": 200,
                            "scrollX": true,
                            scrollY:        '50vh',
                            scrollCollapse: true,
                            paging:         true,
                            ajax: "/php/ebsw/ebis/ajax_d436sdiadminactions.php",
                            columns: [
                                {
                                    data: null,
                                    defaultContent: '',
                                    className: 'select-checkbox',
                                    orderable: false
                                },
                                { data: "table1.id_nbr"},
                                { data: "table2.atis_class"},
                                { data: "table1.action_date"}, 
                                { data: "table1.action_name"}, 
                                { data: "table1.comments"}, 
                                { data: "table1.badge"}, 
                                { data: "table1.action_id"}, 
                                { data: "table1.action_instance"},
                                { data: "table1.time_flag"}
                            ],
                            order:  [[ 8, "desc" ], [2, "desc"]],
                            select: {
                                style:    'os',
                                selector: 'td:first-child'
                            },
                            buttons: [
                                { extend: "edit",  editor: d436sdiadminactions_editor },
                                { extend: "remove", editor: d436sdiadminactions_editor },
                                {
                                    text: 'Refresh',
                                    action: function ( e, dt, node, config ) {
                                        dt.ajax.reload( null, false );
                                    }
                                },
                                {
                                    text: 'Reload',
                                    action: function () {
                                        window.location.assign("/homepages/departments/436/Information_Services/applications/d436_approval_tbl/d436sdiadmin.html"); 
                                    }
                                },
                                {
                                    extend: 'print',
                                    text: 'Print',
                                    header: true,
                                    exportOptions: {
                                        columns: function ( idx, data, node ) {
                                          var isVisible = d436_edit_sdi_adminactions.column( idx ).visible();
                                          var isNotForExport = $.inArray( idx, hideFromExport ) !== -1;
                                          return isVisible && !isNotForExport ? true : false; 
                                        }
                                    }
                                },
                                {               
                                    extend: 'colvis',
                                    text: 'Col Vis',
                                    //collectionLayout: 'fixed two-column',
                                    columns: ':gt(0)'

                                }
                            ],
                            columnDefs: [ 
                                { targets: 1, visible: false},
                                { targets: 6, visible: false},
                                { targets: 7, visible: false},
                                { targets: 8, visible: false},
                                { targets: 9, visible: false}
                            ]
                        } );

                        //d436_edit_sdi_actions.buttons().container()
                        //    .appendTo( $('div.eight.column:eq(0)', d436_edit_sdi_actions.table().container()) );

                        // Apply the search
                        d436_edit_sdi_adminactions.columns().every( function () {
                            var that = this;

                            $( 'input', this.header() ).on( 'keyup change', function () {
                                if ( that.search() !== this.value ) {
                                    that
                                        .search( this.value )
                                        .draw();
                                }
                            } ).on("click", function(e){
                                e.stopPropagation();
                            });
                        } );    

                        d436sdiadminactions_editor.on( 'close', function () {
                            d436_edit_sdi_adminactions.ajax.reload( null, false );
                        } );

                        d436sdiadminactions_editor.on( 'responsive-resize', function ( e, datatable, columns ) {
                        var count = columns.reduce( function (a,b) {
                            return b === false ? a+1 : a;
                        }, 0 );
                        });

                        $('#d436_sdi_adminactions_div').show();
                        $('#d436_sdiactions_customForm_div').show();
                    } );

jdupaul

PHP CODE:

                        date_default_timezone_set('America/New_York');
                        #require connection class
                        require_once 'd436_db_ebwh.php';

                         /*
                         * Example PHP implementation used for the index.html example
                         */
                        // DataTables PHP library
                        include( "edit-php/DataTables.php" );

                        // Alias Editor classes so they are easy to use
                        use
                            DataTables\Editor,
                            DataTables\Editor\Field,
                            DataTables\Editor\Format,
                            DataTables\Editor\Mjoin,
                            DataTables\Editor\Options,
                            DataTables\Editor\Upload,
                            DataTables\Editor\Validate;

                        // Build our Editor instance and process the data coming from _POST

                        $editor = Editor::inst( $db, 'jdupaul.d436_sdi_actions table1', 
                            array(
                                'table1.id_nbr',
                                'table1.action_date',
                                'table1.action_name',
                                'table1.comments',
                                'table1.badge',
                                'table1.action_id',
                                'table1.action_instance',
                                'table1.time_flag')
                            )
                            ->fields(
                                Field::inst( 'table1.id_nbr' )
                                    ->validator( 'Validate::notEmpty' )
                                    ->validator( 'Validate::maxLen', 10 )
                                    ->getFormatter( 'Format::CapitaliseStringValues' )
                                    ->setFormatter( 'Format::CapitaliseStringValues' ),
                                Field::inst( 'table1.action_date' )
                                    ->validator( 'Validate::notEmpty' )
                                    ->validator( 'Validate::dateFormat', array(
                                        "format"  => Format::DATE_ISO_8601,
                                        "message" => "Please enter a date in the format yyyy-mm-dd"
                                    ) )
                                    ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
                                    ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),      
                                Field::inst( 'table1.action_name' )
                                    ->validator( 'Validate::notEmpty' )
                                    ->validator( 'Validate::maxLen', 30 )
                                    ->getFormatter( 'Format::CapitaliseStringValues' )
                                    ->setFormatter( 'Format::CapitaliseStringValues' ),
                                Field::inst( 'table1.comments' )
                                    ->validator( 'Validate::notEmpty' )
                                    ->validator( 'Validate::maxLen', 200 ),
                                Field::inst( 'table1.badge' )
                                    ->validator( 'Validate::notEmpty' )
                                    ->validator( 'Validate::maxLen', 6 )
                                    ->validator( 'Validate::minLen', 6 )
                                    ->validator( 'Validate::numeric' ),
                                Field::inst( 'table1.action_id' )
                                    ->validator( 'Validate::notEmpty' )
                                    ->validator( 'Validate::maxLen', 30 )
                                    ->getFormatter( 'Format::CapitaliseStringValues' )
                                    ->setFormatter( 'Format::CapitaliseStringValues' ),
                                Field::inst( 'table1.action_instance' )
                                    ->validator( 'Validate::notEmpty' )
                                    ->validator( 'Validate::maxLen', 2 )
                                    ->validator( 'Validate::minLen', 1 )
                                    ->validator( 'Validate::numeric' ),
                                Field::inst( 'table1.time_flag' )
                                    ->validator( 'Validate::notEmpty' )
                                    ->validator( 'Validate::dateFormat', array(
                                        "format"  => Format::DATE_ISO_8601,
                                        "message" => "Please enter a date in the format yyyy-mm-dd"
                                    ) )
                                    ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
                                    ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
                                Field::inst( 'table2.atis_class' )
                                ->set(false)
                            )
                            ->leftJoin( 'jdupaul.d436_sdi_process table2', 'table2.id_nbr', '=', 'table1.id_nbr' )
                            ->process( $_POST )
                            ->json();