Editing left join tables
Editing left join tables
shantiram
Posts: 13Questions: 5Answers: 2
Hi,
I'm writing a small SPA using datatables and editor: https://www.yogameditation.com/karto/
Trying to create a few parent/child table pairs as described here: https://datatables.net/blog/2016-03-25
Currently facing two issues:
On joined tables, edits are only registered for the "main" table i.e. if I'm getting a table "student" and joining with "enrolment" then only edits on "student" will be saved. Similar problem on create. Rows for both tables are created, but no foreign key is generated for the "enrolment" row so it never shows up in the datatable. Is there a way to set something like mysqli::$insert_id as the default for a field?
Whenever a row in the parent table is selected, the ajax request for the child table is made like 2-4 times. (have attached a screenshot) Not sure why this is happening.
Happy for any help or suggestions
This question has an accepted answers - jump to answer
Answers
If you could post your data model and your PHP Editor source code that would be great.
There are three tables used in the code below: course, student, and enrolment.
Enrolment acts as a bridge table for student and course.
They are joined on
student.student_id = enrolment.student_id and enrolment.course_id = course.course_id
I haven't set up any actual foreign keys in mysql. Could that be the problem?
new_enrolments.php
course_enrolments.php
...and the last one:
student_enrolments.php
Let's assume you have student as the parent table. You select one student to display the student's enrolments. When you open the enrolments Editor in this case you want to assign further courses to the student. You do this with an options instance in which you display a list to select one (or more) courses to be assigned to the student. In the enrolments Editor you can also enter all the other enrolements attributes.
Likewise you can have courses as the parent table. You select one course to display the course's students. In this case enrolments Editor is used to assign further students to the respective cours etc. etc.
In this example I have "user" as the parent table and I have a table called "creditor_has_user" used to assign a creditor to that user and also to add an attribute which is the user's role for that creditor (but of course you can add as many attributes as you like). So you have "user" and "creditor" and "creditor_has_user" as the link between the two.
This is the editor instance for the link table
By the way if you had a true link table consisting of only two columns (i.e. the ids of the two tables) Editor offers additional functionality. In this case you can edit both tables simultaneously and Editor will automatically maintain the link table. That does NOT work if your link table has its own id and addtional attributes (i.e. if it has more than the two keys from the tables to be linked.).
Here is an example for this.
I have three tables "contract", "underlying" and the connnection "derivative_has_underlying". Underlying is only a view of contract - I am linking the table with itself. "derivative_has_underlying" only has two columns: "derivative_contract_id" and "underlying_contract_id".
In this case you can edit "contract" and - through the mjoin instance - edit "underlying" as well while the link table is updated automatically because it only has the two links and nothing else.
If your link table is more than just the link (two foreign keys) and has its own id this is NOT possible. Your Editor instance must be for the link table in that case and you can only link to the other tables from that link table (i.e. from enrolments you can link to students and to courses). In your example you cannot edit "courses" with an Editor instance for "students" because your link table "enrolments" isn't a true link table but has its own id and contains additional attributes. That means that with your data model you need to put the link table (enrolments) "in focus" if you want to manage your enrolments. It cannot be done from the students editor directly because you aren't able to link to the courses from there. Likewise you can't do it from the courses editor because you can't link to the students from there.
But here is the Mjoin example for a pure link table:
Ok, thanks a lot. So basically I have to either work around this limitation or structure my db differently?
One thing that puzzles me though is that in one scenario it works.
I have a small function that allows you to select one row from the "new enrolments" table and one row from the students table and "merge" them, i.e. update old entry in students with info from new entry in "new enrolments", and then move the enrolment from the new entry to the old entry. (This is used for students who enrol several times.)
In the code for this (part of it below) fields from both sides of the join are edited (enrolment.student_id, student.status).
This seems to work perfectly.
I can't comment on that one, I am afraid.
But here is a link in which Allan (the author of Editor and Data Tables) commented on e.g. compound keys in Mjoins (not supported): https://datatables.net/forums/discussion/43446
and left joins on columns that aren't the primary key (not supported):
https://datatables.net/forums/discussion/42199/bug-editor-ignores-where-clause-in-leftjoin-update#latest
I found a couple of cases where I could not get everything done with Editor. But I have always been able to integrate work arounds.
Sometimes I had to insert the link table myself upon create (I use delete cascade so no worries about the deletes). Or I have really complex options that don't work with the options instance. Here is an example for both. Good luck!
Hi,
Good day.
Thanks for this. Really heped me a lot! Bit more code than I wanted to write, but at least there's a workaround.
Regards.