PHP Editor: Insert on two tables using a One-to-One Join

Dennis14e

Hello,

Is it possible to use a 1-to-1 join in a way that both tables are filled in case of an insert?
Currently only editing and deleting (probably via the foreign key settings) works for me, but not creating.
Only one entry is created in the main table "Assets", but none in the table "Displays".

My current code:

$editor = Editor::inst($db, 'Assets', 'id');

// Main table
$editor->fields(
    Field::inst('Assets.id')
        ->set(Field::SET_CREATE),

    Field::inst('Assets.type_id')
        ->setFormatter(
            function ($val, $data) use ($type) {
                return $type['id'];
            }
        ),

    Field::inst('Assets.status_id')
        ->options(Options::inst()
            ->table('Asset_Status')
            ->value('id')
            ->label('name')
        )
        ->validator('Validate::dbValues'),
    Field::inst('Asset_Status.name'),

    Field::inst('Assets.notes')
        ->setFormatter(
            Format::ifEmpty(null)
        ),
);

// Joined table
$editor->join(
    Join::inst('Displays', 'object')
        ->join('id', 'asset_id')
        ->field(
            Field::inst('resolution')
                ->setFormatter(
                    function ($val, $data) {
                        return strtolower($val);
                    },
                    Format::ifEmpty(null)
                ),

            Field::inst('size')
                ->setFormatter(
                    Format::ifEmpty(null)
                ),
        )
);

$editor->leftJoin('Asset_Status', 'Assets.status_id', '=', 'Asset_Status.id');
$editor->process($_POST);
$editor->json();

Dennis14e

I have now noticed that when I create a new entry I get an PHP error:
I am using DataTables 1.11.3 and Editor 2.0.6.

Warning: array_merge(): Expected parameter 2 to be an array, object given in vendor/datatables.net/editor-php/Editor.php on line 1454
{"data":[{"DT_RowId":"row_1","Assets":{"id":"1","type_id":"2","status_id":"1","notes":null},"Asset_Status":{"name":"Active"},"Displays":{}}]}

allan

I usually recommend a nested editing approach for this kind of thing as it gives you a lot more flexibility and also it is much easier to use!

Using a Join like that, yes it should insert the new record into Displays, but I would strongly recommend against taking this approach. The Join operates in such a way that it deletes the join rows (when using that class), and then inserts new ones when doing an edit. So any other data you have in that row would be lost. This is one of the reasons we are moving towards nested editing first.

Allan

Dennis14e

I understand the usefulness of nested editors in a 1-to-many join, however, not in a 1-to-1 join.
If I understand it correctly, the main table points to the table to be joined (exactly the other way around as I'm using it right now), but how can I be sure e.g. that there are no unused entries in the joining table or that I really use entries only once?
Besides the table "Displays" I will have other tables (e.g. "Computers"), which have different columns. Only the columns in the table "Assets" (the main table) are needed for all tables.
Otherwise, I also wanted to use inline editing, would that work at all with nested editors?

Greetings Dennis

allan

With 1:1 joins, don't use the Join class - instead use leftJoin. It will be much safer!

We'll probably be removing the object option for Join in future (I almost did so for the 2.0 release...).

You are right that inline editing wouldn't work well with nested tables. For such cases a left join, and submit the primary key for the joined table, would do the job.

Allan