Editor unable to create a new entry when working with joined tables

elmospa

Hi, I'm using datatables and editor with PHP.

What I'm trying to do (but unfortunately failing) is creating a record in table prodotti(alfanumerico PK, codice, ...) and simultaneously create one in table descrizioni(alfanumerico PK, desc_breve_it, desc_lunga_it, ...) from one single form submit.

Initially what I got was the SQL error:
Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

but then I tried to fix it and this thread helped a lot. I wasn't aware of that specific use of Join class and join/link method.
So, with the right adjustments, this is my part of PHP editor code:

$data = Editor::inst($db, 'prodotti', 'alfanumerico')
->fields(...)
->join(
        Join::inst('descrizioni', 'object')
            ->join('alfanumerico', 'alfanumerico')
            ->field(
                Field::inst('desc_breve_it'),
                Field::inst('desc_lunga_it')
            )
    )

but, even if the data is correctly sent to the frontend, and the editor on the frontend correctly sends data to the server like:

data[0][prodotti][codice]: prova4
data[0][prodotti][alfanumerico]: 050505050505
...
data[0][descrizioni][desc_breve_it]: test1
data[0][descrizioni][desc_lunga_it]: test2
action: create

The server doesn't seem to manage the incoming data well since they don't get stored.
So I enabled the debugger on PHP editor and took a look at the response. Apparently, there's no trace of 'test1' nor 'test2', and the only 'INSERT' that is done on descrizioni table contains only alfanumerico field.

Am I missing something? Are DT libraries able to accomplish this?
Thank you!

elmospa

update/correction

the 'INSERT' that was happening on descrizioni table was due to this part:

    ->on('writeCreate', function ($editor, $id, $values) {
        $editor->db()->insert('descrizioni', [
            'alfanumerico' => $values['prodotti']['alfanumerico'],
        ]);
    })

and not thanks to the join class and method. If I try to remove that part, the submit completely fails: A system error has occurred (More information).

Then I figured out I could use the same function to add the fields to the new record on descrizioni table. The code then became:

    ->on('writeCreate', function ($editor, $id, $values) {
        $editor->db()->insert('descrizioni', [
            'alfanumerico' => $values['prodotti']['alfanumerico'],
            'desc_breve_it' => $values['descrizioni']['desc_brevelungabreve_it']
        ]);
    })

allan

Your workaround is probably as good as it gets at the moment. Improving the writing on joined instances is something that I very much want to work on in future.

You note that you only want to insert one row - that sounds like it would be better suited for use with a leftJoin?

Allan

elmospa

Thank you for your check!
Yes, actually now that the job is done by the ->on('writeCreate'...) part, the join can be back to:

->leftJoin('descrizioni', 'descrizioni.alfanumerico', '=', 'prodotti.alfanumerico')