Does Editor support "Edit" / "New" with multi-level joins where some elements of the join are empty?

JJG

I have just started using Datatables and Editor so I apologise if this seems obvious.

I have a db structure that is similar to:
t1.id
t1.t2id
t1.data

t2.id
t2.data

t3.id
t3.t2id
t3.data

And display in Editor
t1.data, t2.data, t3.data

which seems to work as expected. However, if I try inline edit t2.data or t3.data for a row where t2 and t3 do not exist I get the error:
An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY'

Is this expected? What is the sop to create rows where they do not exist?
(P.S. This error also occurs when using the new pop-up)

JJG

Update:
So it seems that the t2 and t3 records are being created but that neither t1.t2id nor t3.t2id are being updated with the t2.id of the t2 record being created.

allan

You need to use the submit option of form-options and set it to be allIfChanged when using inline editing. Otherwise it only submits the field that was edited. You also need to make sure that you send the id for the joined tables (usually in hidden fields).

Allan

JJG

Thanks for the quick response. I had found previous posts that covered these issues and had used them as below

editor = new $.fn.dataTable.Editor( {
        ajax: "../php/venues.php",
        table: "#venues",
        formOptions: {
            inline: {
                submit: "AllIfChanged"
            }
        },
        fields: [ {
                label: "Venue",
                name: "feVenue.venueName"
            }, {
                label: "Type",
                name: "feVenue.venueTypeID",
                type: "select"
            }, {
                label: "Destination",
                name: "feVenue.venueDest"
            }, {
.
.
.
            }, {
                label: "Contact",
                name: "feContact.contactFName"
            }, {
                label: "Surname",
                name: "feContact.contactLName"
            }, {
                label: "Position",
                name: "feContact.contactPosition"
            }, {
                label: "Phone",
                name: "fePhone.phoneNumber"
            }, {
                label: "Email",
                name: "feEmail.emailAddress"
            }, {
                name: "feContact.id",
                type: "hidden"
            }, {
                name: "feEmail.id",
                type: "hidden"
            }, {
                name: "fePhone.id",
                type: "hidden"
            }
        ]
    } );

allan

submit: "AllIfChanged"

Should be:

submit: "allIfChanged"

it is case sensitive.

Allan

JJG

I adjusted the case as you point out, but unfortunately have the same behaviour.
I notice that in all your left-Join examples only data in the left table is affected by the editor - do you have any example of editing/creating data in the right table that I could use as a model (I suppose that this was really my original question).

allan

I'm afraid I don't - sorry. Its not normally something I encourage, even if Editor's libraries allow for it.

An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY'

Do you know which table is giving that error?

Also it would be worth enabling the debug mode in the server-side script, which you can do by adding the debug method immediately before the process() one and pass in true to the debug method. The JSON return from the server will show the SQL that Editor is generating which should hopefully indicate here the error is.

Allan

JJG

re: encouragement
My case is constrained in that the right tables' results from the join are unique or null, but agreed on general principle.

The integrity violation disappeared after correcting the capitalisation of allIfChanged.

Looking at the SQL generated after Create:
Editor creates the left table record and then the right table record but does not populate the join key in the left table with the newly created right table record id, so it is left hanging. Given that I cannot get access to the right table record id before it is too late I suppose I could manually create the right table record using the preCreate call back and then overwrite the join key in the left table record before it is submitted...

or just rethink the whole thing :-)

allan

The Editor libraries will insert into the host table first, and then if required insert or update into the left joined tables. So if you need an id from the joined tables before inserting into the host table, I'm afraid that the code would need to be reorganised.

I think that manually creating the joined row in preCreate probably would be how you would need to do this I'm afraid.

Allan