Table join with update on parent (main) table

bacalov

Hello.
Could somebody tell me what I`m missing on this:

I`m displaying in DTE a list of possible options for a field as a select (radio works as well). This select is build with a JOIN to child table - this part works like a charm.
But when performing an update it looks like UPDATE is done on child table (and the "Duplicate primary key" error is returned).

Everything on client looks well... When performing an edit server is getting the correct field values, so my guess is that I`m missing something on the server-side php.

Server side ajax is this:

[code]
$editor = Editor::inst( $db, 'parent_table' ,'PARENT_TABLE_ID')
->fields(
Field::inst( 'PARENT_TABLE_ID' )->set(false),
Field::inst( 'PARENT_TABLE_FULLNAME' )->validator( 'Validate::required' ),
Field::inst( 'PARENT_TABLE_USERNAME' )->validator( 'Validate::required' ),
Field::inst( 'PARENT_TABLE_EMAIL' )->validator( 'Validate::required' ),
Field::inst( 'PARENT_TABLE_CHILD_ID' ), //----- this one is JOINed with child_table.CHILD_ID
Field::inst( 'PARENT_TABLE_CLIENT' ),
Field::inst( 'PARENT_TABLE_ADMINISTRATOR' ),
Field::inst( 'PARENT_TABLE_STATUS' )
)
->join(
Join::inst( 'child_table', 'object' )
->join( 'PARENT_TABLE_CHILD_ID', 'CHILD_ID' )
->set( false )
->field(
Field::inst( 'CHILD_ID' ),
Field::inst( 'CHILD_DISPLAY' )
)

);
$out = $editor
->process($_POST)
->data();

if ( !isset($_POST['action']) ) {
$out['child_table'] = $db
->select( 'owg_be_users_roles', 'CHILD_ID as value, CHILD_DISPLAY as label' )
->fetchAll();
}
echo json_encode( $out );

[/code]

Any help is appreciated.
Thank you.

allan

Hi,

An update is being done on the child table, even although you have `set(false)` in the join!? That certainly sounds wrong! What version of the Editor libraries are you using? I think that parameter was added in the 1.2.3 release...

Regards,
Allan

bacalov

Hi Allan,
The version I`m using is 1.2.4
My mistake... it does not update the child table after [quote]set(false)[/quote], just the parameter is send to the server using the child table fields - sorry for that.
It`s sending all the update parameters correctly to the server and the update is working for all, except the joined field (regardless of the type this field is displayed).

Is there, may be, a debug option integrated to get an echo on the performed queries?

Thank you.

allan

So `PARENT_TABLE_CHILD_ID` isn't being updated? Could you link me to the page, or show me what is being sent to the server?

> Is there, may be, a debug option integrated to get an echo on the performed queries?

Not really unfortunately - that's something I should really add! At the moment you need to hack the SQL driver for the database you are using and add a couple of echo statements to see what the query is (there should be an echo statement there already, just commented out.

Regards,
Allan

bacalov

Thank you for the reply, Allan.
Yes, [quote]PARENT_TABLE_CHILD_ID[/quote] is not updated. The main purpose of the JOIN is to display possible values for [quote]PARENT_TABLE_CHILD_ID[/quote], based on [quote]PARENT_TABLE[/quote] values, that`s why I would prefer not to use any linking tables in this case.
[code]
POST PARAMS SEND TO SERVER
action=edit&
table=&
id=row_1&
data[PARENT_TABLE_FULLNAME]=2Adrian+Bs&
data[PARENT_TABLE_USERNAME]=Bs&
data[PARENT_TABLE_EMAIL]=adi%40localhost.coms&
data[ChildTable][id]=3&
data[PARENT_TABLE_ADMINISTRATOR]=1&
data[PARENT_TABLE_STATUS]=1

UPDATE AND SELECT queries displayed by $sql var in the driver
UPDATE `ParentTable` SET `PARENT_TABLE_FULLNAME` = :PARENT_TABLE_FULLNAME, `PARENT_TABLE_USERNAME` = :PARENT_TABLE_USERNAME, `PARENT_TABLE_EMAIL` = :PARENT_TABLE_EMAIL, `PARENT_TABLE_ADMINISTRATOR` = :PARENT_TABLE_ADMINISTRATOR, `PARENT_TABLE_STATUS` = :PARENT_TABLE_STATUS WHERE `PARENT_TABLE_ID` = :where_0

SELECT `PARENT_TABLE_ID`, `PARENT_TABLE_ID`, `PARENT_TABLE_FULLNAME`, `PARENT_TABLE_USERNAME`, `PARENT_TABLE_EMAIL`, `PARENT_TABLE_ROLE_ID`, `PARENT_TABLE_ADMINISTRATOR`, `PARENT_TABLE_STATUS` FROM `ParentTable` WHERE `PARENT_TABLE_ID` = :where_0

SELECT `ParentTable`.`PARENT_TABLE_ROLE_ID` as _dte_pkey, `ChildTable`.`id` FROM `ParentTable` as ParentTable JOIN `ChildTable` ON `ChildTable`.`id` = `ParentTable`.`PARENT_TABLE_ROLE_ID`

{"id":"row_1","fieldErrors":[],"sError":"","aaData":[],"row":{"DT_RowId":"row_1","PARENT_TABLE_ID":"1","PARENT_TABLE_FULLNAME":"2Adrian Bs","PARENT_TABLE_USERNAME":"Bs","PARENT_TABLE_EMAIL":"adi@localhost.coms",

"PARENT_TABLE_ROLE_ID":"1", // and this is the field which should have an updated value of "3".

"PARENT_TABLE_ADMINISTRATOR":"1","PARENT_TABLE_STATUS":"1","ChildTable":{"id":"1"}}}
[/code]

bacalov

The driver isn`t performing any update on field involved in the JOIN, or at least it seem so.
Could you please point me into right direction to modify default update statement for JOIN cases, or may be add something like:
[code]
UPDATE parentTable
INNER JOIN childTable ON parentTable.childID = childTable.PKey
SET
--- all the regular fields to update here as it is now ---,
parentTable.childID = $valueFromPostForThisField
WHERE parentTable.PKey = parentTable.RowID

[/code]

Thank's again, Allan.