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

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

jdupauljdupaul Posts: 5Questions: 0Answers: 0

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?

Replies

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

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

    Allan

  • jdupauljdupaul Posts: 5Questions: 0Answers: 0

    @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.

  • jdupauljdupaul Posts: 5Questions: 0Answers: 0

    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>
    
  • jdupauljdupaul Posts: 5Questions: 0Answers: 0

    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();
                        } );
    
  • jdupauljdupaul Posts: 5Questions: 0Answers: 0

    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();
    
This discussion has been closed.