MJoin Alias - Records deleted in lookup table when updating
MJoin Alias - Records deleted in lookup table when updating
peterbrowne
Posts: 314Questions: 54Answers: 0
in Editor
I am using multiple Mjoins with aliases. These work OK for creating records, but when updating records, the entries corresponding to the parent table in the the lookup table are deleted.
This was I believe the subject of this post:
https://datatables.net/forums/discussion/46958/why-do-multiple-mjoins-on-tbl-with-alias-work-when-creating-records-breaks-when-updating-records
At that time, Allan, you indicated it would be fixed v1.8...
I'm using https://cdn.datatables.net/responsive/2.2.3/js/dataTables.responsive.min.js
This question relates to a previous question which probably can be removed.
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
Relevant code:
unit_outcome_data.php:
and edit_unit_outcome.php:
.
I think what is missing here is that you don't have a
wherecondition on the Mjoin instance - just on the options.Looking at the code will apply a
wherecondition to the delete command, so hopefully all you need to do as add:Allan
Thanks Allan, but I'm confused as to where this WHERE should go in the following code:
I tried adding
->where('discipline_fk', '5' )just beneath the->linklines, but doing an UPDATE still deletes the entries in the lookup table, instead of UPDATING.If you could clarify how the code above should be changed in more detail please. Also, is the WHERE clause on the options otherwise not needed?
Yes - that should have done it. Can you show me the JSON result from the debug when it added in there?
You would still keep it on the options, since that is generated by a separate query (assuming you want the options for this Mjoin to be limited).
Allan
This is the JSON on adding a new unit_outcome:
There are 7 Mjoins with WHERE clauses to correspond to the 7 selects in the Editor for the 7 medical disciplines (ed, gp, internal_medicine, obs_gyn, paediatrics, psychiatry and surgery) which contain their own discipline outcomes in the selects.
And the table structure and the above insert into the lookup table:
Apologies, I had thought that debug mode was enabled in your script, but apparently not. Could you add
->debug(true)before the->process()method call and show me the resulting JSON from an edit again? It will show the SQL that is being executed then.Allan
Like this?
https://www.dropbox.com/s/ujxfrxu43j63ohr/json.json?dl=0
In the above JSON with embedded query, this was using only two, not all seven, Mjoins and corresponding selects (to make things simpler...).
I have found that the lookup table has, in this case, created the two records in the lookup table when creating a new record.
When updating that record, I notice that only the last select for the Mjoin has an option selected, not the previous Mjoin select (in this case for 2 Mjoins...). But even though I re-select that option in that first select, the FIRST record (the one corresponding to that select) in the lookup table gets deleted, not the record associated with the last Mjoin select.
With all 7 Mjoin selects in the original files, not all have records yet, so some of those selects have no options. Also, the last select had no options as well.
So, if only the last Mjoin was being updated in the logic, then there would have been no record for that last Mjoin to update. Hence all records for that parent record in the lookup table would show as deleted.
It looks like a bug in your code.
I'm not actually seeing a DELETE statement at all in the queries there. What this for a "create" action or an "edit"? Could you also show me the full PHP that you used for when that trace was taken please?
Thanks,
Allan
Allan, there is no delete being intentionally done my end. Just an update. As I said, the create works fine. When updating, all the corresponding records in the lookup tabe get deleted (except possibly for the last Mjoin using an alias) when using aliases.
Code from the edit_unit_outcome.php file (front end). I have simplified this to just have 2 of the selects related to the Mjoins with aliases, not all 7 that I will need.
Code for the unit_outcome_data.php file (server side):
```
<?php
session_start();
$user = str_replace("'", '', $_SESSION['cm_user']['user']);
include( "../../../datatables/lib/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;
Editor::inst( $db_cm_md, 'unit_outcome', 'unit_outcome_pk' )
<?php > ``` ?>->field(
Field::inst( 'unit_outcome.unit_outcome' ),
Field::inst( 'unit_outcome.modified' ),
Field::inst( 'unit_outcome.modified_by' )->setValue( $user )
)
->join(
Mjoin::inst( 'unit' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_unit_outcome_lookup.unit_outcome_fk' )
->link( 'unit.unit_pk', 'unit_unit_outcome_lookup.unit_fk' )
->order( 'unit.unit_name asc' )
->fields(
Field::inst( 'unit_pk' )
->options( Options::inst()
->table( 'unit' )
->value( 'unit_pk' )
->label( 'unit_name' )
),
Field::inst( 'unit_name' )
)
)
->join(
Mjoin::inst( 'program_outcome' )
->link( 'unit_outcome.unit_outcome_pk', 'program_outcome_unit_outcome_lookup.unit_outcome_fk' )
->link( 'program_outcome.program_outcome_pk', 'program_outcome_unit_outcome_lookup.program_outcome_fk' )
->order( 'program_outcome.program_outcome asc' )
->fields(
Field::inst( 'program_outcome_pk' )
->options( Options::inst()
->table( 'program_outcome' )
->value( 'program_outcome_pk' )
->label( 'program_outcome' )
->render( function ( $row ) {
return strip_tags($row['program_outcome']);
} )
->order( 'program_outcome' )
),
Field::inst( 'program_outcome' )
)
)
->join(
Mjoin::inst( 'strand' )
->link( 'unit_outcome.unit_outcome_pk', 'strand_unit_outcome_lookup.unit_outcome_fk' )
->link( 'strand.strand_pk', 'strand_unit_outcome_lookup.strand_fk' )
->order( 'strand.strand_name asc' )
->fields(
Field::inst( 'strand_pk' )
->options( Options::inst()
->table( 'strand' )
->value( 'strand_pk' )
->label( 'strand_name' )
->order( 'strand_name' )
),
Field::inst( 'strand_name' )
)
)
->join(
Mjoin::inst( 'discipline_outcome' )
->name( 'paediatrics' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
->link( 'paediatrics.discipline_outcome_pk', 'unit_outcome_discipline_outcome_lookup.discipline_outcome_fk' )
->where('discipline_fk', '5' )
// ->order( 'discipline_outcome.discipline_outcome asc' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->where( function ($q) {
$q->where( 'discipline_fk', '5' );
}
),
Field::inst( 'discipline_outcome' )
)
)
)
->join(
Mjoin::inst( 'discipline_outcome' )
->name( 'surgery' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
->link( 'surgery.discipline_outcome_pk', 'unit_outcome_discipline_outcome_lookup.discipline_outcome_fk' )
->where('discipline_fk', '7' )
// ->order( 'discipline_outcome.discipline_outcome asc' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->where( function ($q) {
$q->where( 'discipline_fk', '7' );
}
),
Field::inst( 'discipline_outcome' )
)
)
)
->debug(true)
->process($_POST)
->json();
Any ideas??
Apologies for having not been able to reply over the weekend.
Right - but my problem is that I don't see a DELETE statement in the debug information given above. I will try to reproduce the issue locally here.
Allan
I'm starting to get a better handle on this now. The code I linked to above doesn't apply since we are in a proper link table situation here.
When setting up my local example of this I realised that with your code you would only ever have a single option per field - is that correct?
I'm also not clear why you are using
discipline_outcomeas the name for each of the Mjoin instances? The links don't appear to use adiscipline_outcometable. So for example:should be:
I might well be missing something here, but that makes sense does it not?
Allan
Hi Allan
The selects for the Mjoins contain all options related to the discipline in the WHERE clause. This all comes from one table,
discipline_outcomeand the options in the selects related to the discipline are matched via the lookup tableunit_outcome_discipline_outcome_lookupin the WHERE clause.One or more options can be selected from those selects.
If I change:
to
I get an error that the table
surgerydoesn't exist, which it doesn't...and shouldn't as the records are in tablediscipline_outcomes.So, using:
The above works for creating the record, but when doing an UPDATE the corresponding records in the lookup table are deleted, EXCEPT for the LAST Mjoin which is left intact. Note that I just used
->link( 'surgery.discipline_outcome_pk',as a test, but makes no difference using that or->link( 'discipline_outcome.discipline_outcome_pk',, they both work to a point as stated above.Any update on this yet?
Apologies for the delay - in not being able to get back to you yesterday. I didn't have a chance to sit down and think about this one properly!
Looking at the code above, the options for each Mjoin are coming from the
discipline_outcomewith a specific condition (which may have multiple options) - that's fine.What isn't fine, and I think is the underlying issue, is how
unit_outcome_discipline_outcome_lookupis being used as a link / junction table for multiple different Mjoin instances. The Editor libraries don't allow for that. They expect the link table to contain two columns and two columns only: one referencing the host primary key and one referencing the child primary key.The way Editor's PHP libraries work is that rather than trying to do a 'diff' on the submitted data and the data in the database, it is just deletes all relevant entries for the host key, and then inserts the new data.
The upshot of that is that there can only be one Mjoin instance per link table - any subsequent Mjoins attempting to use the same table would delete what has gone before, which is exactly what you are seeing. There is no way for Editor to tell which Mjoin instance a row in the join table belongs to.
It might be possible to extend Mjoin so it can consider a third column (or more) in the link table and apply constriction conditions to that (also inserting the values when the new items are created), but that isn't how it works at the moment when using a link table I'm afraid.
At this time, if you want multiple Mjoins with link tables, you would need multiple link tables - e.g.
unit_outcome_discipline_outcome_lookup_7.Allan
OK, thanks Allan.
Is it possible then to have a select in the editor that is not a join on the main parent table? So a separate data script from the main one?
That way there wouldn't need to be a relationship in the main data script for the parent table. So in the
discipline_outcometable, there is a foreign_key for the tabledisciplineThis way I could have the 7 selects for discipline_outcomes based on the 7 discipline foreign keys.If this is possible, can the separate data script handle all 7 left joins at once, or would I need 7 separate scripts?
You certainly can, but it would just store the value of the select into the parent table, which doesn't sound like what you need.
On the client-side, the
selectis just a bunch of values - I think the problem here is how to make the server-side aspect work, and it doesn't quite line up with how the libraries operate I'm afraid.Allan
OK. I now have 7 lookup tables, one for each discipline, each containing three columns, a primary key column and columns for each of
unit_outcome_fkanddiscipline_outcome_fkTesting with just 2 of those lookup tables, I notice a few things:
If I have:
The discipline_outcome select below is repeated in the editor (in the browser) 5 times.
Even though I have just the one discipline_outcome select:
This may be due to having 5 fields in the editor section of the script:
If I change that part of the script to have 2
discipline_outcomeselects, I don't get ANY records returned on the page or DB error message.I will add the full server and client code below in the next comment.
Client side
edit_unit_outcome.php:and the server side script,
unit_outcome_data.phpNote also, that if I just have the one
discipline_outcome[].discipline_outcome_pkfield in the editor code, it works fine, but I can't get it to work with 2.Do I need to use aliases, if so how?
OK, I used aliases and now works fine so far, except for one smallish problem. So using:
and server side:
The smallish problem is when I do an update, and select one of the discipline_outcome select to 'No selection' to remove the selected entry in that select, the corresponding record in the lookup table is not deleted, but gets a zero value for the discipline outcome instead of the previous value.
I know this is the placeholder value, but what should I use to get the record deleted in this case?
Also, when using aliases, I con't get the Datatables display columns to show the records using:
and
bump
I have asked a separate question in the forum on this specific issue.