Bug in postSubmit, submitSuccess events

rf1234

Good morning Allan,

this code isn't working any longer with the "create" action. It used to work with previous Editor versions but not any longer.

.on( 'postSubmit', function ( e, json, data, action ) {
    if ( action === 'edit' || action === 'create' ) {
        var selected = contractTable.row( {selected: true} ); 
        if (selected.any()) {
            $.ajax({
                type: "POST",
                url: 'actions.php?action=generateCashFlow',
                data: {
                    table: 'variable',
                    id: json.data[0].DT_RowId.substring(4), //only after "row_"
                    contractId: selected.data().contract.id
                }
            });
        }
    }
    ajaxReloadTbls( [contractTable, cashFlowTable] );
});

with the "create" action json.data is an empty array; no DT_RowId, nothing... I also tried this with the submitSuccess event. Same issue. It works fine with the "edit" action. Could you provide a fix please. Many thanks.

allan

Hi,

I fear that this is likely another case of this bug in 1.6.3. It will be fixed in 1.6.4. If I find a workaround before then, I'll post back with it.

Allan

rf1234

thanks

rf1234

Hi Allan, sorry but this bug wasn't fixed in 1.6.4. Just tried it. You mentioned the other bug that you linked to above in the relase note but not this one.
So it still doesn't work with "create", only with "edit". Can this be fixed please.

rf1234

Just checked it with my debugger: json.data is an empty array if action === 'create'. Same problem as before. Will have to go back to 1.6.2 without a fix.

.on('postSubmit', function (e, json, data, action) {
    if (!json.error) {
        if ( (action === 'edit' || action === 'create') &&
              typeof json.data[0] !== 'undefined'           )  {
            var selected = contractTable.row({selected: true});
            if (selected.any()) {
                $.ajax({
                    type: "POST",
                    url: 'actions.php?action=generateCashFlow',
                    data: {
                        table: 'variable',
                        id: json.data[0].DT_RowId.substring(4), //only after "row_"
                        contractId: selected.data().contract.id
                    }
                });
            }
        }
        ajaxReloadTbls([contractTable, cashFlowTable]);
    }
});

allan

Interesting. I've just tried the following on this example:

editor.on( 'postSubmit', function ( e, json, data, action ) {
  console.log( json.data );
} );

and it appears to work without any issues - the data array from the JSON return is shown in the console.

Are you able to give me a link to a page showing the issue? Or perhaps your Editor configuration so I can see if there is any difference between that example and your use case?

Thanks,
Allan

rf1234

Let's try the Editor configuration first. The test case would be rather complex to explain.

This is the configuration:

//Editor default settings!
    if (lang === 'de') {
        $.extend( true, $.fn.dataTable.Editor.defaults, {            
            i18n: {
                remove: {
                    button: "Löschen",
                    title:  "Eintrag löschen",
                    submit: "Endgültig Löschen",
                    confirm: {
                        _: 'Sind Sie sicher, dass Sie die %d ausgwählten Zeilen löschen wollen?',
                        1: 'Sind Sie sicher, dass Sie die ausgewählte Zeile löschen wollen?'
                    }
                },
                edit: {
                    button: "Bearbeiten",
                    title:  "Eintrag bearbeiten",
                    submit: "Änderungen speichern"
                },
                create: {
                    button: "Neuer Eintrag",
                    title:  "Neuen Eintrag anlegen",
                    submit: "Neuen Eintrag speichern"
                },
                datetime: {
            previous: 'Zurück',
            next:     'Weiter',
            months:   [ 'Januar', 'Februar', 'März', 'April', 'Mai', 'Juni', 'Juli', 'August', 'September', 'Oktober', 'November', 'Dezember' ],
            weekdays: [ 'So', 'Mo', 'Di', 'Mi', 'Do', 'Fr', 'Sa' ],
            amPm:     [ 'am', 'pm' ],
            unknown:  '-'
                },
                error: {            
            system: "Ein Systemfehler ist aufgetreten (<a target=\"_blank\" href=\"//datatables.net/tn/12\">Für mehr Informationen</a>)."
        },
                multi: {
            title: "Mehrere Werte",         
                        info: "Die ausgewählten Elemente enthalten verschiedene Werte für das Feld. Um alle Elemente für diess Feld auf den gleichen Wert zu setzen, klicken Sie bitte hier. Ansonsten werden die Elemente ihren jeweiligen Wert behalten.",
            restore: "Änderungen rückgängig machen",
            noMulti: "Dieses Feld kann einzeln bearbeitet werden, aber nicht als Teil einer Gruppe."
        },
            }      
        });
    }

rf1234

var contractFixedEditor = new $.fn.dataTable.Editor({
ajax: {
url: 'actions.php?action=tblContractFixed',
data: function (d) {
    var selected = contractTable.row({selected: true});
    if (selected.any()) {
        d.contract_id = selected.data().contract.id;
        d.contract_further_approvals_creditor = selected.data().contract.further_approvals_creditor;
    }
}
},
table: "#tblContractFixed",
fields: [{
    label: "Element:",
    name: "fixed.element",
    type: "select",
    options: elementOptions
}, {
    label: "Start Date:",
    name: "fixed.start_date",
    attr: {
        class: dateMask
    },
    type: "datetime",
    def: function () {
        return inTwoDays
    },
    format: 'L',
    opts: {
        showWeekNumber: true,
        momentLocale: momentLocale
    }
}, {
    label: "End Date:",
    name: "fixed.end_date",
    attr: {
        class: dateMask
    },
    type: "datetime",
    def: function () {
        return inFiveDays
    },
    format: 'L',
    opts: {
        showWeekNumber: true,
        yearRange: 40,
        momentLocale: momentLocale
    }
}, {
    label: "Amount:",
    name: "fixed.amount",
    attr: {
        class: amountMask
    }
}, {
    label: "Discount:",
    name: "fixed.discount",
    attr: {
        class: amountMask
    }
}, {
    label: "Currency:",
    name: "fixed.currency",
    type: "select",
    options: currencyOptions
}, {
    label: "Rate %:",
    name: "fixed.rate"
}, {
    label: "Effective Rate %:",
    name: "fixed.effective_rate"
}, {
    label: "Fixed Interest Amount (if any):",
    name: "fixed.fixed_interest_amount",
    attr: {
        class: amountMask
    }
}, {
    label: "Rate Calculation Method:",
    name: "fixed.rate_calc_method",
    type: "select",
    options: rateCalcMethodOptions
}, {
    label: "Repayment Method:",
    name: "fixed.repayment_method",
    type: "select",
    options: repaymentMethodOptions
}, {
    label: "Payment Frequency:",
    name: "fixed.payment_frequency",
    type: "select",
    options: paymentFrequencyOptions
}, {
    label: "First Payment Date:",
    name: "fixed.first_payment_date",
    attr: {
        class: dateMask
    },
    type: "datetime",
    def: function () {
        return inFiftyDays
    },
    format: 'L',
    opts: {
        showWeekNumber: true,
        momentLocale: momentLocale
    }
}, {
    label: "Payment Day:",
    name: "fixed.payment_day",
    type: "select",
    options: paymentDayOptions
}, {
    label: "(Initial) Repayment Percent:",
    name: "fixed.repayment_percent",
    type: "select",
    options: repaymentPercentOptions
}, {
    label: "Installment (repayment or annuity):",
    name: "fixed.installment",
    attr: {
        class: amountMask
    }
}, {
    label: "Period Start / End if holiday:",
    name: "fixed.period_rule",
    type: "select",
    options: periodRuleOptions
}, {
    label: "Holiday Calendar:",
    name: "fixed.holiday_calendar",
    type: "select",
    multiple: true,
    separator: '/',
    options: holidayCalendarOptions
}, {
    label: "Adjust interest periods for bank holidays?",
    name: "fixed.rate_calc_adjusted",
    type: "checkbox",
    options: [
        {label: "Yes", value: 1},
    ],
    separator: '',
    unselectedValue: 0
}, {
    label: "Fee (if any):",
    name: "fixed.fee",
    attr: {
        class: amountMask
    }
}, {
    label: "Fee Due Date:",
    name: "fixed.fee_date",
    attr: {
        class: dateMask
    },
    type: "datetime",
    format: 'L',
    opts: {
        showWeekNumber: true,
        momentLocale: momentLocale
    }
}, {
    label: "FRA Reference Rate:",
    name: "fixed.ref_rate",
    type: "select",
    options: refRateOptions
}, {
    label: "FRA Rate Period:",
    name: "fixed.ref_rate_period",
    type: "select",
    options: refRatePeriodOptions
}, {
    label: "FRA Spread %:",
    name: "fixed.spread"
}, {
    label: "FRA End Date:",
    name: "fixed.fra_end_date",
    attr: {
        class: dateMask
    },
    type: "datetime",
    def: function () {
        return inFiftyDays
    },
    format: 'L',
    opts: {
        showWeekNumber: true,
        momentLocale: momentLocale
    }
}, {
    label: "FRA Rate Fixing Rule:",
    name: "fixed.fixing_rule",
    type: "select",
    options: fixingRuleOptions
}
]
});

contractFixedEditor
.dependent('fixed.element', function (val, data, callback) {
    var self = contractFixedEditor;
    setEditorFixedFields(self);
})
.dependent('fixed.repayment_method', function (val, data, callback) {
    var self = contractFixedEditor;
    setEditorRepaymentDependencies(
            self.field('fixed.payment_frequency'), 
            self.field('fixed.repayment_percent'),
            self.field('fixed.repayment_method'),
            self.field('fixed.installment'),
            self);
})
.dependent('fixed.installment', function (val, data, callback) {
    var self = contractFixedEditor;
    setEditorInstallmentDependencies(
            self.field('fixed.repayment_percent'),
            self.field('fixed.repayment_method'),
            self.field('fixed.installment'),
            self);
})
.dependent('fixed.fee', function (val, data, callback) {
    var self = contractFixedEditor;
    if (val != 0 && val > '-') { //if the fee contains a not 0 value
        self.show(['fixed.fee_date']);
        if (self.val('fixed.fee_date') <= '') {
            self.field('fixed.fee_date').set(inTwoDays);
        }
    } else {
        self.set({'fixed.fee_date': ''})
                .hide(['fixed.fee_date']);
    }
})
.on('open', function (e, mode, action) {
    maskAmount();
    maskDateTime();
    if (contractFixedEditor.val('fixed.amount') == 0) {
        contractFixedEditor.set('fixed.amount', '');
    }
    if (contractFixedEditor.val('fixed.discount') == 0) {
        contractFixedEditor.set('fixed.discount', '');
    }
    $('.DTE_Form_Info').addClass("text-warning");
    if (action === 'edit') {
        contractFixedEditor.message(function () {
            return 'If you change this entry all of your corresponding\n\
                    approval requests will be deleted!';
        })
    }

    var selected = contractTable.row({selected: true});
    if (selected.any()) {
        if (selected.data().contract.instrument === 'W') {  //deposit
            this.field('fixed.element')
                    .update(elementOptionsNoDerivativesDeposit, false);
        } else {
            if (selected.data().contract.instrument === 'Z') {
                this.field('fixed.element')
                        .update([], false);
            } else {
                this.field('fixed.element')
                        .update(elementOptionsNoDerivativesLoan, false);
            }
        }
        var i = 0;
        while (elementOptions[i]) {
            if (elementOptions[i].value.substring(0, 2) ===
                    selected.data().contract.derivative) {
                this.field('fixed.element')
                        .update([elementOptions[i]], true);
            } else {
                if (selected.data().contract.derivative === 'X5') {
                    if (i > 1 && //don't select loan or deposit again and no variable instr.
                            elementOptions[i].value.substring(0, 1) !== 'V') {
                        this.field('fixed.element')
                                .update([elementOptions[i]], true);
                    }
                }
            }
            i++;
        }
        if (ck > '' && typeof ck.rfp !== 'undefined' && selected.data().contract.type < 'A'
                && action === 'create') { //if new naked derivative element
            this.set({'fixed.amount': ck.rfp.amount,
                'fixed.discount': ck.rfp.discount,
                'fixed.currency': ck.rfp.currency,
                'fixed.start_date': ck.rfp.start_date,
                'fixed.end_date': ck.rfp.end_date,
                'fixed.rate_calc_method': ck.rfp.rate_calc_method,
                'fixed.repayment_method': ck.rfp.repayment_method,
                'fixed.payment_frequency': ck.rfp.payment_frequency,
                'fixed.first_payment_date': ck.rfp.first_payment_date,
                'fixed.payment_day': ck.rfp.payment_day,
                'fixed.repayment_percent': ck.rfp.repayment_percent,
                'fixed.installment': ck.rfp.installment,
                'fixed.fixing_rule': ck.rfp.fixing_rule,
                'fixed.period_rule': ck.rfp.period_rule,
                'fixed.rate_calc_adjusted': ck.rfp.rate_calc_adjusted,
                'fixed.holiday_calendar': ck.rfp.holiday_calendar,
                'fixed.ref_rate': ck.rfp.ref_rate,
                'fixed.ref_rate_period': ck.rfp.ref_rate_period});
            ck = '';
        }
    }
})
.on('close', function () {
    $('.DTE_Form_Info').removeClass("text-warning");
})
.on('postSubmit', function (e, json, data, action) {
    if (!json.error) {
        if ( (action === 'edit' || action === 'create') &&
              typeof json.data[0] !== 'undefined'           )  {
            var selected = contractTable.row({selected: true});
            if (selected.any()) {
                $.ajax({
                    type: "POST",
                    url: 'actions.php?action=generateCashFlow',
                    data: {
                        table: 'fixed',
                        id: json.data[0].DT_RowId.substring(4), //only after "row_"
                        contractId: selected.data().contract.id
                    }
                });
            }
        }
        ajaxReloadTbls([contractTable, cashFlowTable]);
    }
});

rf1234

I tried it with a much simpler stand alone table - and it worked (checked with debugger). This Editor instance has the same default settings as the one above:

var rateEditor = new $.fn.dataTable.Editor( {
    ajax: {
        url: 'actions.php?action=tblRate'
    },
    table: "#tblRate",
    fields: [ {
            label: "Reference Rate:",
            name:  "rate.ref_rate",
            type:  "select",
            options: refRateOptions
        }, {
            label: "Currency:",
            name:  "rate.currency",
            type:  "select",
            options: liborCurrencyOptions
        }, {
            label: "Rate Period:",
            name:  "rate.ref_rate_period",
            type:  "select",
            options: refRatePeriodOptions
        }, {
            label: "Rate Date:",
            name:  "rate.date",
            attr: {
                class: dateMask
            },
            type:  "datetime",
            def:   function () { return today},
            format: 'L',
            opts:  {
                showWeekNumber: true,
                momentLocale: momentLocale
            }
        }, {
            label: "Rate %:",
            name:  "rate.rate"
        }
    ]        
} );

rateEditor
        .on('open', function(e, mode, action) {                
            maskDateTime();
        })
        .on( 'postSubmit', function ( e, json, data, action ) {
            if (json.error) {
                if ( json.error.includes('1062 Duplicate entry') ) {
                   json.error = 'Sorry, this rate already exists!';
                }
            }
        });

The one above is a child table; will try with the parent table of the above table after lunch and get back to you.

rf1234

Now I tried with the parent table "contract". Works fine! So it does not work with the child table "fixed". It works with the stand alone table "rate" and the parent table of "fixed" which is "contract".

allan

That's the contractFixedEditor Editor is it? I don't see anything immediately wrong with that.

Could you show me the JSON that the server is returning in response to that create Ajax request?

Thanks,
Allan

rf1234

Now to complete the picture I will try with a different child table and see whether I can reproduce the problem:
Tried with table "userphone" which is a simple child table of "user" - and it worked. Now I am really confused ...

rf1234

Yes it is "contractFixedEditor" that has the problem. Hang on will send you the JSON shortly.

rf1234

{data: [], debugSql: [{,…}, {,…}, {,…}, {,…}]}
data
:
[]
debugSql
:
[{,…}, {,…}, {,…}, {,…}]
0
:
{,…}
bindings
:
[{name: ":element", value: "L", type: null},…]
query
:
"INSERT INTO  `fixed`  ( `element`, `start_date`, `end_date`, `amount`, `discount`, `currency`, `rate`, `effective_rate`, `fixed_interest_amount`, `rate_calc_method`, `repayment_method`, `payment_frequency`, `first_payment_date`, `payment_day`, `repayment_percent`, `installment`, `period_rule`, `holiday_calendar`, `rate_calc_adjusted`, `fee`, `fee_date`, `ref_rate`, `ref_rate_period`, `spread`, `fra_end_date`, `fixing_rule`, `update_time`, `updater_id`, `creator_id` ) VALUES (  :element,  :start_date,  :end_date,  :amount,  :discount,  :currency,  :rate,  :effective_rate,  :fixed_interest_amount,  :rate_calc_method,  :repayment_method,  :payment_frequency,  :first_payment_date,  :payment_day,  :repayment_percent,  :installment,  :period_rule,  :holiday_calendar,  :rate_calc_adjusted,  :fee,  :fee_date,  :ref_rate,  :ref_rate_period,  :spread,  :fra_end_date,  :fixing_rule,  :update_time,  :updater_id,  :creator_id )"
1
:
{,…}
bindings
:
[{name: ":where_0", value: "30", type: null}, {name: ":where_1", value: "3", type: null}]
query
:
"SELECT  `fixed`.`id` as 'fixed.id', `fixed`.`element` as 'fixed.element', `fixed`.`start_date` as 'fixed.start_date', `fixed`.`end_date` as 'fixed.end_date', `fixed`.`amount` as 'fixed.amount', `fixed`.`discount` as 'fixed.discount', `fixed`.`currency` as 'fixed.currency', `fixed`.`rate` as 'fixed.rate', `fixed`.`effective_rate` as 'fixed.effective_rate', `fixed`.`fixed_interest_amount` as 'fixed.fixed_interest_amount', `fixed`.`rate_calc_method` as 'fixed.rate_calc_method', `fixed`.`repayment_method` as 'fixed.repayment_method', `fixed`.`payment_frequency` as 'fixed.payment_frequency', `fixed`.`first_payment_date` as 'fixed.first_payment_date', `fixed`.`payment_day` as 'fixed.payment_day', `fixed`.`repayment_percent` as 'fixed.repayment_percent', `fixed`.`installment` as 'fixed.installment', `fixed`.`period_rule` as 'fixed.period_rule', `fixed`.`holiday_calendar` as 'fixed.holiday_calendar', `fixed`.`rate_calc_adjusted` as 'fixed.rate_calc_adjusted', `fixed`.`fee` as 'fixed.fee', `fixed`.`fee_date` as 'fixed.fee_date', `fixed`.`ref_rate` as 'fixed.ref_rate', `fixed`.`ref_rate_period` as 'fixed.ref_rate_period', `fixed`.`spread` as 'fixed.spread', `fixed`.`fra_end_date` as 'fixed.fra_end_date', `fixed`.`fixing_rule` as 'fixed.fixing_rule', `fixed`.`update_time` as 'fixed.update_time', `fixed`.`updater_id` as 'fixed.updater_id', `fixed`.`creator_id` as 'fixed.creator_id' FROM  `fixed` LEFT JOIN `contract_has_fixed` ON `contract_has_fixed`.`fixed_id` = `fixed`.`id` WHERE `contract_has_fixed`.`contract_id` = :where_0 AND `fixed`.`id` = :where_1 "
2
:
{,…}
bindings
:
[{name: ":user_id", value: "21", type: null}, {name: ":table", value: "fixed", type: null},…]
query
:
"INSERT INTO  `log`  ( `user_id`, `table`, `action`, `values`, `changed_id` ) VALUES (  :user_id,  :table,  :action,  :values,  :changed_id )"
3
:
{,…}
bindings
:
[{name: ":contract_id", value: "30", type: null}, {name: ":fixed_id", value: "3", type: null}]
query
:
"INSERT INTO  `contract_has_fixed`  ( `contract_id`, `fixed_id` ) VALUES (  :contract_id,  :fixed_id )"

rf1234

And this is the JSON response from an update (which is ok):

{data: [{DT_RowId: "row_3",…}],…}
data
:
[{DT_RowId: "row_3",…}]
0
:
{DT_RowId: "row_3",…}
DT_RowId
:
"row_3"
fixed
:
{id: "3", element: "L", start_date: "17.08.2017", end_date: "20.08.2017", amount: "-12,34",…}
amount
:
"-12,34"
creator_id
:
"21"
currency
:
"EUR"
discount
:
""
effective_rate
:
"1,0000"
element
:
"L"
end_date
:
"20.08.2017"
fee
:
""
fee_date
:
"17.08.2017"
first_payment_date
:
"04.10.2017"
fixed_interest_amount
:
""
fixing_rule
:
""
fra_end_date
:
""
holiday_calendar
:
"1"
id
:
"3"
installment
:
""
payment_day
:
"31"
payment_frequency
:
"1"
period_rule
:
"B"
rate
:
"1,0000"
rate_calc_adjusted
:
"0"
rate_calc_method
:
"A1"
ref_rate
:
""
ref_rate_period
:
""
repayment_method
:
"A"
repayment_percent
:
"101"
spread
:
""
start_date
:
"17.08.2017"
update_time
:
"2017-08-15 15:12:24"
updater_id
:
"21"
debugSql
:
[{query: "SELECT * FROM `fixed` WHERE `fixed`.`id` = :where_0 ",…}, {,…}, {,…}, {,…}]
0
:
{query: "SELECT * FROM `fixed` WHERE `fixed`.`id` = :where_0 ",…}
bindings
:
[{name: ":where_0", value: "3", type: null}]
query
:
"SELECT  * FROM  `fixed` WHERE `fixed`.`id` = :where_0 "
1
:
{,…}
bindings
:
[{name: ":element", value: "L", type: null},…]
query
:
"UPDATE  `fixed` SET  `element` = :element, `start_date` = :start_date, `end_date` = :end_date, `amount` = :amount, `discount` = :discount, `currency` = :currency, `rate` = :rate, `effective_rate` = :effective_rate, `fixed_interest_amount` = :fixed_interest_amount, `rate_calc_method` = :rate_calc_method, `repayment_method` = :repayment_method, `payment_frequency` = :payment_frequency, `first_payment_date` = :first_payment_date, `payment_day` = :payment_day, `repayment_percent` = :repayment_percent, `installment` = :installment, `period_rule` = :period_rule, `holiday_calendar` = :holiday_calendar, `rate_calc_adjusted` = :rate_calc_adjusted, `fee` = :fee, `fee_date` = :fee_date, `ref_rate` = :ref_rate, `ref_rate_period` = :ref_rate_period, `spread` = :spread, `fra_end_date` = :fra_end_date, `fixing_rule` = :fixing_rule, `update_time` = :update_time, `updater_id` = :updater_id WHERE `fixed`.`id` = :where_0 "
2
:
{,…}
bindings
:
[{name: ":where_0", value: "30", type: null}, {name: ":where_1", value: "3", type: null}]
query
:
"SELECT  `fixed`.`id` as 'fixed.id', `fixed`.`element` as 'fixed.element', `fixed`.`start_date` as 'fixed.start_date', `fixed`.`end_date` as 'fixed.end_date', `fixed`.`amount` as 'fixed.amount', `fixed`.`discount` as 'fixed.discount', `fixed`.`currency` as 'fixed.currency', `fixed`.`rate` as 'fixed.rate', `fixed`.`effective_rate` as 'fixed.effective_rate', `fixed`.`fixed_interest_amount` as 'fixed.fixed_interest_amount', `fixed`.`rate_calc_method` as 'fixed.rate_calc_method', `fixed`.`repayment_method` as 'fixed.repayment_method', `fixed`.`payment_frequency` as 'fixed.payment_frequency', `fixed`.`first_payment_date` as 'fixed.first_payment_date', `fixed`.`payment_day` as 'fixed.payment_day', `fixed`.`repayment_percent` as 'fixed.repayment_percent', `fixed`.`installment` as 'fixed.installment', `fixed`.`period_rule` as 'fixed.period_rule', `fixed`.`holiday_calendar` as 'fixed.holiday_calendar', `fixed`.`rate_calc_adjusted` as 'fixed.rate_calc_adjusted', `fixed`.`fee` as 'fixed.fee', `fixed`.`fee_date` as 'fixed.fee_date', `fixed`.`ref_rate` as 'fixed.ref_rate', `fixed`.`ref_rate_period` as 'fixed.ref_rate_period', `fixed`.`spread` as 'fixed.spread', `fixed`.`fra_end_date` as 'fixed.fra_end_date', `fixed`.`fixing_rule` as 'fixed.fixing_rule', `fixed`.`update_time` as 'fixed.update_time', `fixed`.`updater_id` as 'fixed.updater_id', `fixed`.`creator_id` as 'fixed.creator_id' FROM  `fixed` LEFT JOIN `contract_has_fixed` ON `contract_has_fixed`.`fixed_id` = `fixed`.`id` WHERE `contract_has_fixed`.`contract_id` = :where_0 AND `fixed`.`id` = :where_1 "
3
:
{,…}
bindings
:
[{name: ":user_id", value: "21", type: null}, {name: ":table", value: "fixed", type: null},…]
query
:
"INSERT INTO  `log`  ( `user_id`, `table`, `action`, `values`, `changed_id` ) VALUES (  :user_id,  :table,  :action,  :values,  :changed_id )"

allan

The JSON response from the create shows data: [] - so postSubmit appears to be correct in saying that the data array is an empty array, since it is.

The question therefore becomes, why is the server not sending data back for that created row?

Might the WHERE condition not be satisfied?

Allan

rf1234

Hi Allan, I looked at the debug SQL myself and I think I understand the problem now:
I have three tables here "contract", "contract_has_fixed" (which is a link table) and "fixed". If I select a contract (parent table) I can create, edit or delete a child element "fixed".
Upon create of a "fixed" element Editor unfortunately does not automatically insert the link table "contract_has_fixed" which only has the two id's. Hence I do the insert myself on "postCreate".
As I see in the debug SQL Editor reads the inserted record from "fixed" immediately after the insert. At that time the link table insert has not been done. As you can see it comes later. The result is therefore empty! That explains the empty json response.

Question: How can I make editor read the inserted record only after the insert of the link table? Or, if that's not possible, how can I make Editor insert the link table directly so that I don't have to do it manually myself?

this is the PHP Editor instance:

function tblContractFixed(&$db, &$lang) {
    if ( ! isset($_POST['contract_id']) || ! is_numeric($_POST['contract_id']) ) {
        echo json_encode( [ "data" => [] ] );
    } else {
        $_SESSION['linkTableParentId'] = $_POST['contract_id'];
        if ($lang === 'de') {     
            $msg[0] = 'Feld darf nicht leer sein.';
        } else {
            $msg[0] = 'Field may not be empty.';
        }
        Editor::inst( $db, 'fixed' )
        ->field(
            Field::inst( 'fixed.id' )->set( false ),
            Field::inst( 'fixed.element' ),
            Field::inst( 'fixed.start_date' )
                    ->getFormatter( function ( $val, $data, $opts ) {
                        return getFormatterDate($val);                 
                    } )
                    ->setFormatter( function ( $val, $data, $opts ) {
                        return setFormatterDate($val);
                    } ),
        ......
            Field::inst( 'fixed.update_time' )  ->set(Field::SET_BOTH)
                                                ->setValue( mySqlTimestamp() ),
            Field::inst( 'fixed.updater_id' )   ->set(Field::SET_BOTH)
                                                ->setValue( $_SESSION['id'] ),
            Field::inst( 'fixed.creator_id' )   ->set(Field::SET_CREATE)
                                                ->setValue( $_SESSION['id'] )        
        )
        ->leftJoin( 'contract_has_fixed', 'contract_has_fixed.fixed_id', '=', 'fixed.id')
        ->where( 'contract_has_fixed.contract_id', $_POST['contract_id'] )
        ->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
            logChange( $editor->db(), 'create', $id, $row, 'fixed' );
            insertLinkTable( $editor->db(), $id, 'contract_has_fixed' );
        } )
        ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
            logChange( $editor->db(), 'edit', $id, $row, 'fixed' );
    //          all approvals need to be requested again upon editing
    //          but not if the contract was manually entered by a gov entity!!
                if (isset ($_POST['contract_further_approvals_creditor'])) {
                    $_SESSION['deleteRfaHasTablesId'] = $id;
                    $_SESSION['cleanUpRfaTask'] = 'CC';
                    if ( ! (bool)$_POST['contract_further_approvals_creditor']) {
                        $_SESSION['selfApprovalId'] = $id;
                        $_SESSION['selfApprovalTask'] = 'CC';
                    }
                }
        } )
        ->on( 'postRemove', function ( $editor, $id, $values ) {
            logChange( $editor->db(), 'delete', $id, $values, 'fixed' );
            $_SESSION['cleanUpCashFlows'] = 'clean';
        } )
        ->debug(true)
        ->process($_POST)
        ->json();
    }
}

allan

Use the writeCreate event rather than postCreate. writeCreate will trigger before Editor gets the data from the database.

Allan

rf1234

This is overlapping, Allan! Will try writeCreate. My problem is I mostly don't understand those subtle differences between the events ... a little more detail in the description could be helpful sometimes ...

in the meantime I developed this:

tried now various versions of left join and Mjoin but no success.

Then I followed this example https://editor.datatables.net/examples/advanced/joinLinkTable.html in a very strict manner. In order to imitate what's going on in the example I created some kind of "pseudo-interaction" with the front-end even though it seems to be useless ... but anyway this way I could persuade Editor to do the right thing: Insert the link table BEFORE reading back the values. I would call this a work around.

PHP: I expanded the left join also to include the "contract" table. Secondly I added an options instance for contract_has_fixed.contract_id which is being filled with contract.id. I defined a pseudo label "type" which nobody sees at the front end. And of course I deleted the manual "insertLinkTable".

.....
Field::inst( 'fixed.creator_id' )   ->set(Field::SET_CREATE)
                                                ->setValue( $_SESSION['id'] ),
            Field::inst( 'contract_has_fixed.contract_id' )
                   ->options( Options::inst()
                        ->table( 'contract' )
                        ->value( 'id' )
                        ->label( 'type' )
                    )
        )
        ->leftJoin( 'contract_has_fixed', 'contract_has_fixed.fixed_id', '=', 'fixed.id')
        ->leftJoin( 'contract', 'contract.id', '=', 'contract_has_fixed.contract_id')
        ->where( 'contract.id', $_POST['contract_id'] )
        ->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
            logChange( $editor->db(), 'create', $id, $row, 'fixed' );
//            insertLinkTable( $editor->db(), $id, 'contract_has_fixed' );
        } )

Javascript: I added a field type "hidden" called "contract_has_fixed.contract_id". I preset the field with the selected contract's id. Then contract.id gets returned from the client and can be inserted automatically into the link table.

var contractFixedEditor = new $.fn.dataTable.Editor({
        ajax: {
            url: 'actions.php?action=tblContractFixed',
            data: function (d) {
                var selected = contractTable.row({selected: true});
                if (selected.any()) {
                    d.contract_id = selected.data().contract.id;
                    d.contract_further_approvals_creditor = selected.data().contract.further_approvals_creditor;
                }
            }
        },
        table: "#tblContractFixed",
        fields: [       {
                label: "FRA Rate Fixing Rule:",
                name: "fixed.fixing_rule",
                type: "select",
                options: fixingRuleOptions
            }, {
                type: "hidden",
                name: "contract_has_fixed.contract_id"
            }
        ]
contractFixedEditor
    .on('open', function (e, mode, action) {
            var selected = contractTable.row({selected: true});
                if (selected.any()) {
                    this.set({ 'contract_has_fixed.contract_id':
                                    selected.data().contract.id });
                }
     });

rf1234

No, I am wrong: WriteCreate is described really well. I missed it because it only came in 1.6.2 and I built these things with 1.5.x Thanks again!

PHP: This is my final solution - no front end changes required just this:

->leftJoin( 'contract_has_fixed', 'contract_has_fixed.fixed_id', '=', 'fixed.id')
        ->where( 'contract_has_fixed.contract_id', $_POST['contract_id'] )
        ->on( 'writeCreate', function ( $editor, $id, $values ) {
            insertLinkTable( $editor->db(), $id, 'contract_has_fixed' );
        } )
        ->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
            logChange( $editor->db(), 'create', $id, $row, 'fixed' );
        } )

rf1234

@allan, in one of the examples above I got the left join insert into the link table working but not with a regular left join but I had to use an options instance, send stuff to the client that isn't really needed etc ..
Is it possible to avoid this? I mean to achieve this without all of this and without a “manual“ insert of the link table?

allan

You can update to a left joined table, but I don't think you can currently insert to it. The reason is that if the primary key for the left joined table is submitted, that will be used as the where condition for which row to write to. But if the primary key is not submitted, it is assumed that the table shouldn't be written to, rather than being inserted to.

I've wondered about this before and how it could be improved. Perhaps submitting null for the primary key or an empty string should allow an insert.

Allan

rf1234

"Perhaps submitting null for the primary key or an empty string should allow an insert."

Would be a great improvement for Editor 1.6.5 later this week
And less work in the forums for you