Editor: link and edit two tables

mp2000

Hello,

my problem concerns the editor:

i am at a loss as to how i can link two tables one to one and edit the individual fields of both tables.
I would like to be able to edit the "NoteInternal" and "Hours" fields in the editor.

The desired result should be:
A data set from "ITSM_Maintenance_TaskAsset" is linked to only one data set from "TimeAccounting

2 Tables:

ITSM_Maintenance_TaskAsset
----
id
NoteInternal
timeaccounting_id (linked with TimeAccounting.id)


TimeAccounting
---
id
Hours

<?php
require_once '../../../../configs/main.php';
require_once '../../../../libs/datatables-editor/php/DataTables.php';

use DataTables\Editor;
use DataTables\Editor\Field;
use DataTables\Editor\Join;

$data = Editor::inst($dbIntranet, 'ITSM_Maintenance_TaskAsset')
    ->debug(true)
    ->fields(

        Field::inst('ITSM_Maintenance_TaskAsset.id')
            ->set(false),

        Field::inst('ITSM_Maintenance_TaskAsset.NoteInternal'),

        Field::inst('ITSM_Maintenance_TaskAsset.timeaccounting_id')
    )

//only this did not work, the left joined table will not update
//->leftJoin('TimeAccounting', 'TimeAccounting.id', '=', 'ITSM_Maintenance_TaskAsset.timeaccounting_id')

//this throws an error
//Join was performed on the field 'timeaccounting_id' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance.

    ->join(
        Join::inst('TimeAccounting', 'object')
            ->join('timeaccounting_id', 'id')
            ->field(
                Field::inst('id')->set(false),
                Field::inst('Hours')
            ))

    ->process($_POST)
    ->data();

echo json_encode($data);

allan

Use the leftJoin method, but make sure that you include the TimeAccounting.id field in the client-side fields that will be submitted (use hidden so the end user doesn't see it). Editor needs the primary key value to be able to edit the second table.

Allan

mp2000

Hello Allan,

thanks for your quick reply.
I have created a test table, but it does not yet work as intended.
furthermore, only entries in "TEST_Table1" are created/updated

fields: [{
            name: 'TEST_Table2.id',
            type: 'hidden'
        }, {
            label: "Name:",
            name: "TEST_Table1.Name",
            type: "text",
            multiEditable: false
        }, {
            label: "Note:",
            name: "TEST_Table2.Note",
            type: "text",
            multiEditable: false
        }, ]

<?php
require_once '../../../configs/main.php';
require_once '../../../libs/datatables-editor/php/DataTables.php';

use DataTables\Editor;
use DataTables\Editor\Field;

$data = Editor::inst($dbIntranet, 'TEST_Table1')
    ->debug(true)
    ->fields(

        Field::inst('TEST_Table1.id')
            ->set(false),

        Field::inst('TEST_Table1.Name'),

        Field::inst('TEST_Table1.test_table2_id')
    )
    ->leftJoin('TEST_Table2', 'TEST_Table2.id', '=', 'TEST_Table1.test_table2_id')
    ->process($_POST)
    ->data();
echo json_encode($data);

Parameter
--
action  create
data[0][TEST_Table1][Name]  ddsfsdfds
data[0][TEST_Table2][id]    
data[0][TEST_Table2][Note]  sdfs

Response
--
{"data":[{"DT_RowId":"row_3","TEST_Table1":{"id":"3","Name":"ddsfsdfds","test_table2_id":null}}],"debug":[{"query":"INSERT INTO `TEST_Table1` ( `Name` ) VALUES ( :Name )","bindings":[{"name":":Name","value":"ddsfsdfds","type":null}]},{"query":"SELECT `TEST_Table1`.`id` as 'TEST_Table1.id', `TEST_Table1`.`Name` as 'TEST_Table1.Name', `TEST_Table1`.`test_table2_id` as 'TEST_Table1.test_table2_id' FROM `TEST_Table1` LEFT JOIN `TEST_Table2` ON `TEST_Table2`.`id` = `TEST_Table1`.`test_table2_id` WHERE `TEST_Table1`.`id` = :where_0 ","bindings":[{"name":":where_0","value":"3","type":null}]}]}

allan

You need to add Field::inst('TEST_Table2.id') in your list of fields in the PHP. Otherwise the value isn't included in the data read from the database, which is why it is then being submitted as empty.

Allan

mp2000

unfortunately it still doesn't work:

<?php
require_once '../../../configs/main.php';
require_once '../../../libs/datatables-editor/php/DataTables.php';

use DataTables\Editor;
use DataTables\Editor\Field;

$data = Editor::inst($dbIntranet, 'TEST_Table1')
    ->debug(true)
    ->fields(

        Field::inst('TEST_Table1.id')
            ->set(false),

        Field::inst('TEST_Table1.Name'),
        
        Field::inst('TEST_Table2.id'),
        Field::inst('TEST_Table1.test_table2_id')
    )
    ->leftJoin('TEST_Table2', 'TEST_Table2.id', '=', 'TEST_Table1.test_table2_id')
    ->process($_POST)
    ->data();
echo json_encode($data);

action  create
data[0][TEST_Table1][Name]  Test1
data[0][TEST_Table2][id]    
data[0][TEST_Table2][Note]  Test2

{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'id' at row 1","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"INSERT INTO `TEST_Table1` ( `Name` ) VALUES ( :Name )","bindings":[{"name":":Name","value":"Test1","type":null}]},{"query":"SELECT * FROM `TEST_Table2` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"","type":null}]},{"query":"INSERT INTO `TEST_Table2` ( `id` ) VALUES ( :id )","bindings":[{"name":":id","value":"","type":null}]}]}

allan

Ah - this is on create. Does it work on edit before we attempt the create?

Allan

mp2000

Hello Allan,

only if I add the entries in both tables manually and link them together beforehand, the subsequent editing in "datatables" is functional:

TEST_Table1
--
id | Name | test_table2_id
INSERT INTO `TEST_Table1` VALUES (3, 'Test1', 1);

TEST_Table2
--
id | Note
INSERT INTO `TEST_Table2` VALUES (1, 'Blub');

action  edit
data[row_3][TEST_Table1][Name]  Test1
data[row_3][TEST_Table2][id]    1
data[row_3][TEST_Table2][Note]  change+to+test123

{"data":[{"DT_RowId":"row_3","TEST_Table1":{"id":"3","Name":"Test1","test_table2_id":"1"},"TEST_Table2":{"id":"1","Note":"change to test123"}}],"debug":[{"query":"SELECT * FROM `TEST_Table1` WHERE `TEST_Table1`.`id` = :where_0 ","bindings":[{"name":":where_0","value":"3","type":null}]},{"query":"UPDATE `TEST_Table1` SET `Name` = :Name WHERE `TEST_Table1`.`id` = :where_0 ","bindings":[{"name":":Name","value":"Test1","type":null},{"name":":where_0","value":"3","type":null}]},{"query":"SELECT * FROM `TEST_Table2` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"1","type":null}]},{"query":"UPDATE `TEST_Table2` SET `id` = :id, `Note` = :Note WHERE `id` = :where_0 ","bindings":[{"name":":id","value":"1","type":null},{"name":":Note","value":"change to test123","type":null},{"name":":where_0","value":"1","type":null}]},{"query":"SELECT `TEST_Table1`.`id` as 'TEST_Table1.id', `TEST_Table1`.`Name` as 'TEST_Table1.Name', `TEST_Table2`.`id` as 'TEST_Table2.id', `TEST_Table1`.`test_table2_id` as 'TEST_Table1.test_table2_id', `TEST_Table2`.`Note` as 'TEST_Table2.Note' FROM `TEST_Table1` LEFT JOIN `TEST_Table2` ON `TEST_Table2`.`id` = `TEST_Table1`.`test_table2_id` WHERE `TEST_Table1`.`id` = :where_0 ","bindings":[{"name":":where_0","value":"3","type":null}]}]}

but if i want to create a new entry:

{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'id' at row 1","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"INSERT INTO `TEST_Table1` ( `Name` ) VALUES ( :Name )","bindings":[{"name":":Name","value":"NewName","type":null}]},{"query":"SELECT * FROM `TEST_Table2` WHERE `id` = :where_0 ","bindings":[{"name":":where_0","value":"","type":null}]},{"query":"INSERT INTO `TEST_Table2` ( `id`, `Note` ) VALUES ( :id, :Note )","bindings":[{"name":":id","value":"","type":null},{"name":":Note","value":"NewNote","type":null}]}]}

allan

I'm with you now - thanks! The problem is that Editor's edit action will attempt to insert into the main table first and the joined tables second. That means the entry for the joined table hasn't yet been created and thus there is no primary key value to write into the main table for that link.

I'm afraid this is a limitation of the PHP libraries at this time. It is something we plan to address for 1.8 though.

At the moment you'd need to edit the other tables and then edit the master table.

Allan

mp2000

Okay, when will it come out

allan

I don't have a firm release date yet I'm afraid. I expect it to be August though.

Allan

naspersgasp

Hi,

Good day.

Having similar issues. Is this a PHP limitation only? Or does node suffer from the same problem?

Regards.

icefield

Running into this issue with Editor 1.9.0.

From Alan above:

I'm with you now - thanks! The problem is that Editor's edit action will attempt to insert into the main table first and the joined tables second. That means the entry for the joined table hasn't yet been created and thus there is no primary key value to write into the main table for that link.

I'm afraid this is a limitation of the PHP libraries at this time. It is something we plan to address for 1.8 though.

At the moment you'd need to edit the other tables and then edit the master table.

I'm using leftJoin to an Address table from a User table. I'm trying to provide UI where the user can add (and/or update) their address. The record in the Address table is being created, but I'm not getting the address primary key record linked into the main user table record.

I do have the record id's (from both tables) as part of the Editor fields.

Looking for guidance.

allan

I'm afraid that's not something the Editor libraries provides support for at the moment. It never made it into 1.8 or 1.9 (its something that would have held up those releases).

As I mentioned before, the workaround is to have your user edit two different tables I'm afraid. Our you could use server-side event handlers to check if a new contact is needed or needs to be updated.

Allan