Child table not updating

Child table not updating

ccanteyccantey Posts: 11Questions: 3Answers: 0
edited January 2023 in Free community support

I'm using fairly complex left joins. I am unable to CRUD anything on the child table following this example: parent/child blog

My child table renders correctly. I can CRUD everything correctly in the UI without errors, but the database does not CRUD. No errors are thrown. But the debug statement has no update SQL statements, only SELECT.

child table php

if ( ! isset($_POST['commissionid']) || ! is_numeric($_POST['commissionid']) ) {
    //echo $_POST['commission'];
    echo json_encode( [ "data" => [] ] );
}
else {
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'commissions', 'commid' )
        ->debug( true )
        ->fields(
            Field::inst( 'commissions.commid' )->set(false),
            Field::inst( 'm.memid' ),
            Field::inst( 'm.fname' ),
            Field::inst( 'm.lname' ),
            Field::inst( 'mi.party' ),
            Field::inst( 'mct.session' ),
            Field::inst( 'mct.term_start' ),
            Field::inst( 'mct.term_end' ),
            Field::inst( 'mct.officer' ),
            Field::inst( 'mct.representing' ),
            Field::inst( 'mct.appt_by' )
        )
        ->leftJoin( 'members_commissions AS mc', 'mc.commission_id', '=', 'commissions.commid' )
        ->leftJoin( 'members AS m', 'm.memid', '=', 'mc.member_id' )
        ->leftJoin( 'member_info AS mi', 'mi.memid', '=', 'm.memid' )
//the following left join makes the many join unique, otherwise you get duplicate values
        ->leftJoin( 'member_comm_term AS mct', 'mct.memid', '=', 'm.memid AND (mct.commid = commissions.commid)')
        ->where('commissions.commid', $_POST['commissionid'])
        ->process( $_POST )
        ->json();
}

child table/editor js

    var members_editor = new $.fn.dataTable.Editor( {
        ajax: {
            url:'/appointments/editor/lib/table.members.php',
            data: function (d){

                var selected = commissions_table.row({selected: true});
                
                if (selected.any()){
                    console.log(selected.data())
                    d.commissionid = selected.data().commid;
                }
            }
        },
        table: '#members',
        fields: [
            {
                "label": "First Name:",
                "name": "m.fname"
            },
            {
                "label": "Last Name:",
                "name": "m.lname"
            },
            {
                "label": "memid:",
                "name": "m.memid"
            },
            {
                "label": "commid:",
                "name": "commissions.commid"
            },
            {
                "label": "Party:",
                "name": "mi.party"
            },
            {
                "label": "Session:",
                "name": "mct.session"
            },
            {
                "label": "Start Date:",
                "name": "mct.term_start",
                "type": "datetime"
            },
            {
                "label": "End Date:",
                "name": "mct.term_end",
                "type": "datetime"
            },
            {
                "label": "Officer:",
                "name": "mct.officer"
            },
            {
                "label": "Representing:",
                "name": "mct.representing"
            },
            {
                "label": "Appointed By:",
                "name": "mct.appt_by"
            }
        ]
    } );


    var members_table = $('#members').DataTable( {

        dom: 'QBfrtip', //https://datatables.net/examples/basic_init/dom
         ajax: {
            url: '/appointments/editor/lib/table.members.php',
            type: 'post',
            data: function (d){

                var selected = commissions_table.row({selected: true});
                
                if (selected.any()){
                    console.log(selected.data().commissions.commid)
                    d.commissionid = selected.data().commissions.commid;
                }
            }
        },
        serverSide:true,
  
        columns: [
            {
                data: 'commissions.commid'
            },
            {
                data: 'm.memid'
            },
            {
                data: 'm.fname'
            },
            {
                data: 'm.lname'
            },
            {
                data: 'mi.party'
            },
            {
                data: 'mct.session'
            },
            {
                data: 'mct.term_start' 
            },
            {
                data: 'mct.term_end'
            },
            {
                data: 'mct.officer'
            },
            {
                data: 'mct.representing'
            },
            {
                data: 'mct.appt_by'
            }
        ],
        select: true,
        lengthChange: false,
        buttons: [
                { extend: 'create', editor: members_editor, enabled: false, init: function(dt) {
                var that = this;
                commissions_table.on('select deselect', function() {
                    that.enable(commissions_table.rows({selected: true}).any())
                })
            }},
                { extend: 'edit',   editor: members_editor },
                { extend: 'remove', editor: members_editor },
                {
                extend: 'collection',
                text: 'Export',
                buttons: [
                    'copy',
                    'excel',
                    'csv',
                    'pdf',
                    'print'
                ]
            }
        ],
        searchBuilder: {
            preDefined: {
                criteria:[
                    {  
                        condition: '=',
                        data: 'Session',
                        origData: 'mct.session',
                        type: 'string',
                        value: ['92nd']
                    }
                ],
                logic: 'AND'
            }
        }
    } );

    commissions_table.on( 'select', function (e) {
        //console.log(commissions_table.row( { selected: true } ).data().commid)
        members_table.ajax.reload();
 
        members_editor
            .field( 'commissions.commid' )
            .def( commissions_table.row( { selected: true } ).data().commid);
            //console.log(commissions_table.row( { selected: true } ).data().commid)
    } );
 
    commissions_table.on( 'deselect', function () {
        members_table.ajax.reload();
    } );
     
    members_editor.on( 'submitSuccess', function () {
        commissions_table.ajax.reload();
    } );
 
    commissions_editor.on( 'submitSuccess', function () {
        members_table.ajax.reload();
    } );

} );

Answers

  • ccanteyccantey Posts: 11Questions: 3Answers: 0
    edited January 2023

    I simplified the child table php to this:

        Editor::inst( $db, 'members as m', 'memid' )
            ->debug( true )
            ->fields(
                Field::inst( 'mc.commission_id' )->set(false),
                Field::inst( 'm.memid' ),
                Field::inst( 'm.fname' ),
                Field::inst( 'm.lname' ),
                Field::inst( 'mi.party' ),
                Field::inst( 'mct.session' ),
                Field::inst( 'mct.term_start' ),
                Field::inst( 'mct.term_end' ),
                Field::inst( 'mct.officer' ),
                Field::inst( 'mct.representing' ),
                Field::inst( 'mct.appt_by' )
            )
            ->leftJoin( 'members_commissions AS mc', 'mc.member_id', '=', 'm.memid' )
            ->leftJoin( 'member_info AS mi', 'mi.memid', '=', 'm.memid' )
                   //the following left join makes the many join unique, otherwise you get duplicate values
            ->leftJoin( 'member_comm_term AS mct', 'mct.memid', '=', 'm.memid AND (mct.commid = mc.commission_id)')
            ->where('mc.commission_id', $_POST['commissionid'])
            ->process( $_POST )
            ->json();
    

    as it had some reduncy to parent table:

    Editor::inst( $db, 'commissions', 'commid' )
        ->debug( true )
        ->fields(
            Field::inst( 'commissions.commid' ),
            Field::inst( 'commissions.name' ),
            Field::inst( 'commissions.name_abbrev' )
        )
        
    
        ->join(
            Mjoin::inst( 'members_commissions' )
                ->link( 'commissions.commid', 'members_commissions.commission_id' )
                ->fields(
                    Field::inst( 'member_id' )
                )
                
        )
    
        ->process( $_POST )
        ->json();
    

    mjoin on child might be more elegant but I cant find documentation on creating a view, per previous question: mjoin question

    This does not solve problem, but maybe makes the question easier to read?

  • ccanteyccantey Posts: 11Questions: 3Answers: 0

    I am seeing an empty data array

    if ( ! isset($_POST['commissionid']) || ! is_numeric($_POST['commissionid']) ) {
        //echo $_POST['commission'];
        echo json_encode( [ "data" => [] ] );
    }
    
  • ccanteyccantey Posts: 11Questions: 3Answers: 0
    edited January 2023

    `

  • ccanteyccantey Posts: 11Questions: 3Answers: 0

    d.commissionid = selected.data().commid;

    should have been

    d.commissionid = selected.data().commissions.commid;

Sign In or Register to comment.