Why do multiple mjoins on tbl with alias work when Creating Records, breaks when updating records
Why do multiple mjoins on tbl with alias work when Creating Records, breaks when updating records
rlangille2
Posts: 10Questions: 3Answers: 0
Hello, I have a test case.
retracted
I am trying to assign users to different templates. I have a Persons table, a Roles table, and a PersonRoles table. The idea is that I can assign different PersonRoleIDs to a TemplateID in the PersonRoleTemplates table when creating or updating a record in the Templates2 page. This is because someone might have an Admin role, but for only 1 template. While somebody else may have both an Admin role and an Analyst role but for only 2 templates.
The issue is that this code below works correctly when creating a record on the templates2 page. The page is also reading the json data correctly when you select a row and click edit. However, when you click update, the 'users' that have been assigned does not update correctly, in fact they get deleted, but for only 1 of the 2 mjoins. This is true if you edit who is assigned, and this is true when you hit update without changing who is assigned.
I cannot figure out the issue because the create works like a charm. I could use some help. I need to mention that the table 'PersonInfo' is a stored query. This is because I need to pull in more columns on the mjoin so that I can display info related to the person in the subtable (drill down) + and - icons, and so I can use the Name of the person in the checkboxes on the form.
The purpose of the $q->where( 'RoleID', '1', '=' ); and $q->where( 'RoleID', '2', '=' ); in pi1 and pi2 is so that the query being used in the mjoin only contains the correct RoleID, in this case, RoleID 1 = Admin, RoleID2 = Analyst.
PS - 'Users' come from the same table. They have different PersonRoleIDs in the case of 1 person have more than 1 role assigned to them. So I am performing more than 1 mjoin on the same table, in this case, a query with additional information. The completed project will have at least 4 mjoins on the same users query, all with alias pi1, pi2, pi3, and pi4.
/* Formatting function for row details - modify as you need */
function format ( d ) {
var mystring = ""
for (var i=0; i < d.pi1.length; i++){
var mystring = mystring + '<td>'+d.pi1[i].FName+'</td>'
};
var mystring2 = ""
for (var i=0; i < d.pi2.length; i++){
var mystring = mystring + '<td>'+d.pi2[i].FName+'</td>'
};
// `d` is the original data object for the row
return '<table id="testtable" cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
'<tr>'+
'<td>Report Desc:</td>'+
'<td>'+d.Templates.ReportDesc+'</td>'+
'</tr>'+
'<tr>'+
'<td>Admin</td>'+
mystring +
'<td>Analyst</td>'+
mystring2 +
'</tr>'+
'</table>';
}
var editor; // use a global for the submit and return data rendering in the examples
$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
ajax: "../../php/Scripts/templates2.php",
table: "#example",
fields: [ {
label: "Report Name:",
name: "Templates.ReportName"
}, {
label: "Report Title:",
name: "Templates.ReportTitle"
}, {
label: "Report Desc:",
name: "Templates.ReportDesc"
}, {
label: "Frequency:",
name: "Templates.FrequencyID",
type: "select"
}, {
label: "DueDate Report Inbound:",
name: "Templates.NextDueDatePackager"
}, {
label: "DueDate Report Outbound:",
name: "Templates.NextDueDateRecipient"
},{
"label": "Admin:",
"name": "pi1[].PersonRoleID",
"type": "checkbox"
},{
"label": "Analyst:",
"name": "pi2[].PersonRoleID",
"type": "checkbox"
}
]
} );
var table = $('#example').DataTable( {
dom: "Bfrtip",
ajax: {
url: "../../php/Scripts/templates2.php",
type: 'POST'
},
columns: [
{
"className": 'details-control',
"orderable": false,
"data": null,
"defaultContent": ''
},
{ data: "Templates.ReportName"},
{ data: "Templates.ReportTitle" },
{ data: "Frequencies.Frequency" },
{ data: "Templates.NextDueDatePackager" },
{ data: "Templates.NextDueDateRecipient" }
],
select: true,
buttons: [
{ extend: "create", editor: editor },
{ extend: "edit", editor: editor },
{ extend: "remove", editor: editor }
]
} );
// Add event listener for opening and closing details
$('#example tbody').on('click', 'td.details-control', function () {
var tr = $(this).closest('tr');
var row = table.row( tr );
if ( row.child.isShown() ) {
// This row is already open - close it
row.child.hide();
tr.removeClass('shown');
}
else {
// Open this row
row.child( format(row.data()) ).show();
tr.addClass('shown');
}
});
});
<?php
// DataTables PHP library
include( "../DataTables.php" );
// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;
/*
* Example PHP implementation used for the joinLinkTable.html example
*/
Editor::inst( $db, 'Templates', 'TemplateID' )
->field(
Field::inst( 'Templates.ReportName' ),
Field::inst( 'Templates.ReportTitle' ),
Field::inst( 'Templates.ReportDesc' ),
Field::inst( 'Templates.FrequencyID' )
->options( Options::inst()
->table( 'Frequencies' )
->value( 'FrequencyID' )
->label( 'Frequency' )
),
Field::inst( 'Frequencies.Frequency' ),
Field::inst( 'Templates.NextDueDatePackager' ),
Field::inst( 'Templates.NextDueDateRecipient' )
)
->leftJoin( 'Frequencies', 'Frequencies.FrequencyID', '=', 'Templates.FrequencyID' )
->Join(
Mjoin::inst( 'PersonInfo' )
->where( function ($q) {
$q->where( 'RoleID', '1', '=' );
})
->name( 'pi1' )
->link( 'Templates.TemplateID', 'PersonRoleTemplates.TemplateID' )
->link( 'PersonInfo.PersonRoleID', 'PersonRoleTemplates.PersonRoleID' )
->order( 'PersonID asc' )
->fields(
Field::inst( 'PersonRoleID' )
->validator( Validate::required() )
->options( Options::inst()
->table( 'PersonInfo' )
->value( 'PersonRoleID' )
->label( 'FName' )
->where( function ($r) {
$r->where( 'RoleID', '1', '=' );
})
),
Field::inst( 'PersonID' ),
Field::inst( 'FName' ),
Field::inst( 'RoleID' )
)
)
->Join(
Mjoin::inst( 'PersonInfo' )
->where( function ($q) {
$q->where( 'RoleID', '2', '=' );
})
->name( 'pi2' )
->link( 'Templates.TemplateID', 'PersonRoleTemplates.TemplateID' )
->link( 'PersonInfo.PersonRoleID', 'PersonRoleTemplates.PersonRoleID' )
->order( 'PersonID asc' )
->fields(
Field::inst( 'PersonRoleID' )
->validator( Validate::required() )
->options( Options::inst()
->table( 'PersonInfo' )
->value( 'PersonRoleID' )
->label( 'FName' )
->where( function ($r) {
$r->where( 'RoleID', '2', '=' );
})
),
Field::inst( 'PersonID' ),
Field::inst( 'FName' ),
Field::inst( 'RoleID')
)
)
->process($_POST)
->json();
This question has an accepted answers - jump to answer
Answers
Excellent question - thank you.
The answer lies in how Editor's PHP libraries operate for an Mjoin with a link table on edit. Basically what it does is a
deleteon the link table where the host id matches the row being edited - in this case that would be something like:Then it does an
insertwith the new data.So what is happening here is that on
pi1it is doing the delete then insert, then forpi2it does the same again, but that delete removes the record that was inserted forpi1!It uses this delete / insert for editing linked tables, as there is an assumption (incorrect in this case) that the link table is only used for linking that specific instance.
As a short term workaround, if you where to define two different link tables, one for pi1 and one for pi2, it should work without issue.
Longer term, I think the fix will be do modify the
deleteon edit to use thewherecondition that you have provided for the link. I need to try and think if that would cause any issues through before implementing.Allan
Thank you, the quick fix is working!
Ideally modifying the delete on edit to use the where condition is preferable, since this data should be stored in the same link table for the sanity of db admins. Hopefully that gets added at some point to DataTables.
This is a great piece of software you're developing. I will recommend it to anybody that needs to perform CRUD operations in their web application.
can you please remove my test case and debug code from the opening post now that we've found a solution? - Thank you!
Yes, not a problem - I've removed them now.
Thank you for your kind words - can I quote you on that?
I'm building a little compilation of quotes for an updated landing page...
Regarding adding this in - I've been thinking about it over the course of the day, and I think it is the correct thing to do, so I've added it in as a bug rather than a feature request. I'm not sure if I'll get it in for 1.7.1 (which will be out next week), but it will be in the 1.7.x series.
Allan
i have the same issue. i need one global link table "CMDB_Link_Assets"
I'm afraid it isn't currently possible. It is something that I plan to add to the server-side libraries though.
Allan
Hi Allan,
Did this bug in the opening post get fixed in a current release yet?
Thanks!
Kind regards,
Ryan
Hi Ryan,
Not yet I'm afraid. This is likely to fall into 1.8 now (although hopefully that shouldn't be too far away).
Allan