Parent/Child setup : Error: Undefined binding(s) detected when compiling WHERE. Undefined column(s)

MarkAndersonUK

Hi,

I am sure this is something stupid I have missed, but I was adapting one of the Parent/Child example for my setup and everything works, except when I edit a Child record, and update it, I am getting an error.

Error: Undefined binding(s) detected when compiling WHERE. Undefined column(s): [recruits_emails.recruitID] query: where recruits_emails.recruitID = ?

I am using NodeJS and Server-side. below is my basic setup :

NodeJS

router.all('/api/recruits', async function(req, res) {
    let editor = new Editor(myDB, 'recruits', 'recruits.recruitID')
        .fields(
            new Field("recruits.recruitID"),
            new Field("recruits.NPN"),
            new Field("recruits.FirstName"),
            new Field("recruits.MiddleName"),
            new Field("recruits.LastName")

    await editor.process(req.body);
    res.json(editor.data());
});

router.all('/api/recruits_emails', async function(req, res) {

// The example this code is based on,  does not test the 'req.body.action',   
// so the the system normally just returns an empty data response and stops the edit 
// routine here.  To get around this,  and to get the error displayed,  I added another  
// IF to detect the req.body.action type and if 'edit'  to continue.         I suspect that,  
// if this was working correctly, the req.body.recruitID should have already been set at  
// this point and my added IF would not be needed.

    if (! req.body.recruitID) {
    if (req.body.action=='edit') {
        res.json({data: []});
        return;
}

    let editor = new Editor(myDB, 'recruits_emails', 'recruits_emails.EmailID')
        .fields(
            new Field("recruits_emails.EmailID"),
            new Field("recruits_emails.recruitID"),
            new Field("recruits_emails.isDefaultEmail"),
            new Field("recruits_emails.EmailType"),
            new Field("recruits_emails.EmailAddr")
        )

        .leftJoin('recruits', function () {
            this.on('recruits_emails.recruitID', '=', 'recruits.recruitID')
        })

        .where( function () {
            this.where( 'recruits_emails.recruitID', req.body.recruitID );
        } )

    await editor.process(req.body);
    res.json(editor.data());
});

module.exports = router;

HTML Page / Javascript

        var recruitEditor = new $.fn.dataTable.Editor({
            ajax: '/Recruits/api/recruits',
            table: '#recruits',
            template: '#recruitsForm',
            fields: [
                {label: "Recruit ID:", name: "recruits.recruitID"},
                {label: "NPN ID:", name: "recruits.NPN"},
                {label: "First Name:", name: "recruits.FirstName"},
                {label: "Middle Name:", name: "recruits.MiddleName"},
                {label: "Last Name:", name: "recruits.LastName"}
            ]
        });


        var recruit_table = $('#recruits').DataTable({
            dom: 'Blfrtip',
            ajax: {
                url: '/Recruits/api/recruits',
                type: "POST"
            },
            serverSide: true,
            processing: false,
            columns: [
                {data: "recruits.recruitID"},
                {data: "recruits.NPN"},
                {data: "recruits.FirstName"},
                {data: "recruits.MiddleName"},
                {data: "recruits.LastName"},
                {data: null, className: "dt-center editor-edit", defaultContent: '<i class="fa fa-pen"/>', orderable: false, searchable: false },
                {data: null, className: "dt-center editor-delete", defaultContent: '<i class="fa fa-trash"/>', orderable: false, searchable: false }
            ],
            scrollY: '25vh',
            paging: true,
            buttons: [
                {extend: "create", editor: recruitEditor},
                {extend: "edit", editor: recruitEditor}
            ],
            select: true,
        });

        var r_emailEditor = new $.fn.dataTable.Editor({
            ajax: {
                url: '/Recruits/api/recruits_emails',
                data: function (d) {
                    var selected = recruit_table.row({selected: true});
                    if (selected.any()) {
                        d.recruitID = selected.data().recruitID;
                    }
                }
            },
            table: '#recruits_emails',
            template: '#r-EmailForm',
            fields: [
                {label: "EmailID:", name: "recruits_emails.EmailID"},
                {label: "recruitID:", name: "recruits_emails.recruitID"},
                {label: "isDefaultEmail:", name: "recruits_emails.isDefaultEmail"},
                {label: "Email Type:", name: "recruits_emails.EmailType"},
                {label: "Email Address:", name: "recruits_emails.EmailAddr"}
            ]
        });

        var r_email_table = $('#recruits_emails').DataTable({
            dom: 'Blfrtip',
            ajax: {
                url: '/Recruits/api/recruits_emails',
                type: "POST",
                data: function (d) {
                    var selected = recruit_table.row({selected: true});
                    if (selected.any()) {
                        d.recruitID = selected.data().recruits.recruitID;
                    }
                }
            },
            serverSide: true,
            processing: false,
            columns: [
                {data: "recruits_emails.EmailID"},
                {data: "recruits_emails.recruitID"},
                {data: "recruits_emails.isDefaultEmail"},
                {data: "recruits_emails.EmailType"},
                {data: "recruits_emails.EmailAddr"},
            ],
            buttons: [
                {
                    extend: 'create', editor: r_emailEditor, enabled: false, init: function (dt) {
                        var that = this;
                        recruit_table.on('select deselect', function () {
                            that.enable(recruit_table.rows({selected: true}).any())
                        })
                    }
                },
                {extend: "edit", editor: r_emailEditor}
            ],
            select: true,
        });


        recruit_table.on('select', function (e) {
            r_email_table.ajax.reload();

            r_emailEditor
                .field('recruits_emails.recruitID')
                .def(recruit_table.row({selected: true}).data().recruitID);
        });

        recruit_table.on('deselect', function () {
            r_email_table.ajax.reload();
        });

        r_emailEditor.on('submitSuccess', function () {
            recruit_table.ajax.reload();
        });

        recruitEditor.on('submitSuccess', function () {
            r_email_table.ajax.reload();
        });

I know the problem lies around the req.body.recruitID not being set at the point the edit action is occurring, but I am not sure why this is so, or how to fix it correctly. I was able to cobble together a fix that works, but I suspect it is not the correct way to fix this, so I am asking for your help.

My untidy fix was to change the IF statement at the start of the 'router.all('/api/recruits_emails...' section. to be :

    if (! req.body.recruitID) {
        if (req.body.action!='edit') {
            res.json({data: []});
            return;
        } else {
            req.body.recruitID = req.body.data.row_1.recruits_emails.recruitID;
        }
    }

This works, but I know it is not the correct way to fix this.

MarkAndersonUK

Actually, my fix doesn't work, as it always selects the first row. not the currently selected row.

allan

d.recruitID = selected.data().recruitID;

should be

d.recruitID = selected.data().recruits.recruitID;

Allan

MarkAndersonUK

thanks @allan

I could have sworn I tried that, but I guess I didn't.. As I suspected
a dumb error on my part. I really appreciate you always being on the ball @allan ,

thanks