Bug: Editor ignores WHERE-clause in LEFTJOIN Update
Bug: Editor ignores WHERE-clause in LEFTJOIN Update
rf1234
Posts: 2,986Questions: 87Answers: 421
I am using this statement to SELECT and UPDATE contract approvals. While the statement selects data from multiple tables it only updates one table called contract_has_rfa. The two key tables are contract and contract_has_rfa. There can be multiple rfa's for one contract, but only one for a contract and one user (unique index on contract_has_rfa.contract_id and contract_has_rfa.approver_id). The link to the contract table is through contract.id as a foreign key (contract_has_rfa.contract_id.
Contract_has_rfa is also linked to the user table with user.id which is contract_has_rfa.approver_id in contract_has_rfa.
The statement selects all contracts that are assigned to the respective user ($_SESSION['id']) for approval. This works fine. Only the assigned contracts are selected not all of them. When making the update (setting contract_has_rfa.status to 'W', 'N' or 'A') there is a bug: The UPDATE statement does NOT contain the approver_id in its WHERE clause. It only has the respective contract.id in its WHERE clause. Hence all rfa's get updated for the respective contract - even those assigned to a different user!
The bug is that the UPDATE statement generated by Editor ignores this WHERE clause in my PHP statement below:
->where( 'contract_has_rfa.approver_id', $_SESSION['id'] )
while the SELECT statement uses it. I used the debug option to check the UPDATE statement generated by Editor to verify this.
Is there a fix for this bug? I would like to avoid a work around if possible.
<?php
Editor::inst( $db, 'contract' )
->field(
Field::inst( 'gov.name' )->set( false ),
Field::inst( 'govdept.name' )->set( false ),
Field::inst( 'creditor.name' )->set( false ),
Field::inst( 'contract.id' )->set( false ),
Field::inst( 'contract.description' )->set( false ),
Field::inst( 'contract_has_rfa.status' )
->setFormatter( function ( $val, $data, $opts ) {
if ($val === 'W' && $data['contractStatusAlias'] === 'N') {
$val = 'N';
}
return $val;
} ),
Field::inst( 'contract_has_rfa.status AS contractStatusAlias' )->set( false ),
Field::inst( 'contract_has_rfa.update_time' )->set(Field::SET_BOTH)
->setValue( date("Y-m-d H:i:s") ),
Field::inst( 'contract_has_rfa.updater_id' )->set(Field::SET_BOTH)
->setValue( $_SESSION['id'] )
)
// show approvals if any
->join(
Mjoin::inst( 'user' )
->link( 'contract.id', 'contract_has_rfa.contract_id')
->link( 'user.id', 'contract_has_rfa.approver_id' )
->order( 'contract_has_rfa.status DESC, user.lastname ASC' )
->fields(
Field::inst( 'user.firstname AS userFirstName' )->set( false ),
Field::inst( 'user.lastname AS userLastName' )->set( false ),
Field::inst( 'contract_has_rfa.status AS approvalStatus' )->set( false ),
Field::inst( 'contract_has_rfa.update_time AS updateTime' )->set( false )
->getFormatter( function ( $val, $data, $opts ) {
return getFormatterDateTime($val);
} )
)
)
->join(
Mjoin::inst( 'file' )
->link( 'contract.id', 'contract_has_file.contract_id' )
->link( 'file.id', 'contract_has_file.file_id' )
->fields(
Field::inst( 'web_path' )->set( false ),
Field::inst( 'name' )->set( false )
)
)
->leftJoin( 'govdept', 'contract.govdept_id', '=', 'govdept.id')
->leftJoin( 'gov', 'govdept.gov_id', '=', 'gov.id')
->leftJoin( 'creditor', 'contract.creditor_id', '=', 'creditor.id')
->leftJoin( 'contract_has_rfa', 'contract.id', '=', 'contract_has_rfa.contract_id')
->leftJoin( 'user', 'contract_has_rfa.approver_id', '=', 'user.id')
->where( 'contract_has_rfa.approver_id', $_SESSION['id'] )
->debug(true)
->process($_POST)
->json();
This is the SQL for the UPDATE generated by Editor:
bindings: [{name: ":status", value: "A", type: null},…]
0:{name: ":status", value: "A", type: null}
name:":status"
type:null
value:"A"
1:{name: ":update_time", value: "2017-05-07 16:16:59", type: null}
name:":update_time"
type:null
value:"2017-05-07 16:16:59"
2:{name: ":updater_id", value: "37", type: null}
name:":updater_id"
type:null
value:"37"
3:{name: ":where_0", value: "25", type: null}
name:":where_0"
type:null
value:"25"
query:"UPDATE `contract_has_rfa`
SET `status` = :status, `update_time` = :update_time, `updater_id` = :updater_id
WHERE `contract_id` = :where_0 "
And this is the result in the table contract_has_rfa:
CONTRACT_HAS_RFA after update:
-
id contract_id approver_id who status update_time updater_id creator_id
1 25 37 G A 07.05.2017 16:16 37 6
2 25 12 G A 07.05.2017 16:16 37 6
As you can see both rows where updated while only the first row should have been updated.
This question has accepted answers - jump to:
Answers
Its the
contract_has_rfatable that is having the issue is it? You really don't want to useMJoinwith a link table that has any data other than the simple references to the other tables. The meta information (who, status, etc) would be deleted because of how it works!Basically when you update an Mjoin it will remove the existing rows that match the record from the host row, and then insert new ones with the data submitted. It doesn't do a
diffas it would be complicated and unreliable.Allan
It is the contract_has_rfa table that is having the issue. That is correct. I am using an Mjoin but only to display things, not to update anything. All fields in the Mjoin that includes contract_has_rfa are set to false.
I removed both Mjoins (the one for the approvals and the one for the files). And the result was exactly the same: Both rows get updated through the LEFTJOIN even though it should be just the first one!
Please help! This is fundamental for me. If I can't update with a left join I am really stuck.
This is the modified statement now - but the bug is still there:
I tried something else: My primary table is no longer "contract" but "contract_has_rfa"
I replaced the Mjoin for the approvals overview by a custom SELECT (because it is no longer working if the primary table is the Mjoin link table ...) but no problem.
Didn't mention previously that I have a comments field with an Editor of its own. The comments field is called comments_govdept
Whith the changed approach (primary table "contract_has_rfa") it no longer updates!
The good news is that the update of contract_has_rfa worked because it is no longer done through a LEFTJOIN (i.e. only one contract_has_rfa record gets updated, see previous post above).
In a nutshell: I can choose between a wrong update of contract_has_rfa (orginial approach with two updated records) or a non-update of the contract.comments_govdept field (modified approach) ... Hmm ... I have a serious problem I guess.
This is the SQL Editor generates when updating the comments in the modified approach:
As you can see it only updates contract_has_rfa because it seems to "think" that an update of contract_has_rfa occured even though this didn't happen. Hence it updates the two "auto-update" columns of that table, but does nothing else.
This is the updated Editor instance for the modified approach:
For the sake of completeness this is the Javascript Editor instance for the comments field which works if "contract" is the primary table:
quillToolbar and quillLink are global variables set elsewhere
What I would suggest doing is to add
Field( 'contract_has_rfa.id' )->set( false )into your PHP field set, and then:into the Editor Javascript field list.
To be able to edit the left joined table it needs the primary key to be submitted. It sounds like something is going wrong in the pattern matching as it shouldn't be updating the left joined table at all if the pkey for it isn't being submitted.
Allan
Followed your advice, but it doesn't work. This SQL is being generated by Editor which only specifies contract.id and does not specify contract_has_rfa.id:
For completeness this is my PHP:
And this is my Javascript Editor instance:
I built a work around in order to be able to move on. I am still interest in a fix for this issue though! In case you encounter a similar problem this works:
- set all fields of the joined table to false (in order to avoid Editor updating them)
- use 'preEdit' to do the required updates of the joined table manually
I have two javascript Editor instances (one for the comments field that may not be updated through preEdit) and one for the approval checkboxes that define the status.
Ah! Its because a left join is being done on the
contract_idrather than theidof that joined table. It is expecting that the left join being done on a field which has a unique value (left join is 1:1 after all), and thus it would be able to address the row uniquely by that value.That doesn't work in this case because of that extra where condition. I'm afraid that is not something that the Editor PHP libraries currently handle. I did attempt to use the provided where statements at one point in the past, but they can be so complex that it was just proving to be impossible to get it right.
Sorry - your workaround would be needed at the moment!
Allan
Thanks Allan. Can you mention this in the docs in a suitable place please.
Without the new debug feature to check the SQL generated it would have been extremely hard to figure this out ...
Yup - I need to add documentation about the ability to edit left joined tables, as that isn't actually mentioned at the moment. I'll make sure that this is included.
Allan
@allan, I thought about your response once again. At the time you wrote it I didn't understand it and I still don't really get what you mean. So here it is again:
"Ah! Its because a left join is being done on the contract_id rather than the id of that joined table. It is expecting that the left join being done on a field which has a unique value (left join is 1:1 after all), and thus it would be able to address the row uniquely by that value.
That doesn't work in this case because of that extra where condition. I'm afraid that is not something that the Editor PHP libraries currently handle. I did attempt to use the provided where statements at one point in the past, but they can be so complex that it was just proving to be impossible to get it right."
You say that a left join is 1:1 after all. Well the result of a left join should be 1:1 but the relationship between the "table" and the "joinedTable" is usually 1:n, in some occasions, if a record in the "joinedTable" is optional it may be 1:1. In case a record in the "joinedTable" is mandatory the 1:1 relationship is not necessary; the "joinedTable" record should be part of the "table" record.
Since most of the times the relationship between "table" and "joinedTable" is 1:n I don't understand why you expect that the left join should be done on a field which has a unique value. How should that work? If the "table" key is a foreign key in the "joinedTable" it cannot be unique if the relationship is 1:n. Hence you need a where clause to return only one record of the "joinedTable" and not n records. In a 1:n relationship the "joinedTable" will almost always contain n records that have the same foreign key value. On the other hand side I cannot do a left join on a unique field of the "joinedTable" because I don't know what its values are and that field is of course not part of the "table". If I knew what value of the "joinedTable" primary key was the right one I wouldn't need the left join in the first place!
What am I missing here? Is it really true that I can actually only left join update tables that have a 1:1 relationship? Only in that case I don't need a where clause to limit selection in the "joinedTable" because both primary key of the "table" and the respective foreign key in the "joinedTable" are unique by definition.
This is crucial issue it bothers me also.
Is there a way to include where statement when update
I agree, this is a crucial issue and it bothers me every once in a while when I have forgotten about my work around and get caught by this again ...