Strange field value is set for INSERT query on joined table

fvntly

I'm using the PHP Editor libraries to feed a Datatable.
Tables structure and relationship is products(CODE[pk], PRICE, ...) and descriptions(CODE[fk], DESC_it, DESC_en, ...).
In PHP I'm using
->leftJoin( 'descriptions', 'descriptions.code', '=', 'products.code' ) and I guess it's working properly since I can succesfully display and edit product descriptions on the frontend.

However, when I try to create a new entry, even if I set correctly every field, on the bottom of the create form this SQL error appears:
An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails ('productdb'.'descriptions', CONSTRAINT 'FK_descriptions_products' FOREIGN KEY ('code') REFERENCES 'products' ('code') ON DELETE CASCADE ON UPDATE CASCADE).

So I enabled the debug and on the network tab of chrome dev tools I took a look at the INSERT query.
As it has to be, it first inserts the product and then inserts the description. However, what I think it's wrong is the value for the code field it is trying to set for the description query. For reference, after the INSERT query on products table (which is correct), this is the rest of the code:

...
        {
            "query": "SELECT  * FROM  `descriptions` WHERE `code` = :where_0 ",
            "bindings": [
                {
                    "name": ":where_0",
                    "value": "114",
                    "type": null
                }
            ]
        },
        {
            "query": "INSERT INTO  `descriptions`  ( `DESC_it`, `code` ) VALUES (  :DESC_it,  :code)",
            "bindings": [
                {
                    "name": ":DESC_it",
                    "value": "Example of description trying to be set for a product.",
                    "type": null
                },
                {
                    "name": ":code",
                    "value": "114",
                    "type": null
                }
            ]
        }

I have no clue where that "114" is coming from. It appears that it's trying to set a 3 digit incremental number (that increases by 1 on each request) for the code field on descriptions.

allan

Hi,

Can you show me the full initialisation code for both the server-side, and the client-side (for the DataTable and Editor)? Also being able to see the data submitted to the server would be useful.

I suspect it is entering this part of the code, although why it is incrementing I'm not sure.

Normally when a left join table needs to be updated (which appears to be the case here?), I would suggest doing it via nested editing.

Allan

fvntly

Hi Allan,
The payload that Editor sends to PHP Editor when creating a new entry is:

data[0][products][code]: BBBBBBBBBBBB
data[0][descriptions][desc_it]: this is a test
action: create

The PHP script is:

$data = Editor::inst( $productdb, 'products', 'code' )
    ->idPrefix( '' )
    ->fields(
        Field::inst( 'products.code' ),
        Field::inst( 'descriptions.desc_it' )
            ->getFormatter( function ( $val, $data ) {
                          return is_null($val)
                              ? ''
                              : $val;
              } )
            ->setFormatter( function ( $val, $data ) {
                          return $val == ''
                              ? null
                              : $val;
              } )
  )
    ->leftJoin( 'descriptions', 'descriptions.code', '=', 'products.code' )
        ->debug(true)
    ->process( $_POST )
    ->json();

I don't know if this is actually enough to troubleshoot, in case I would need to clean up the code a bit first. Thank you!

allan

I think this will be the piece of code that is relevant - it attempts to see if there is a row that matches the condition, and if so updates it, otherwise it will insert. That said, it should just do an insert on create without checking if the row exists... Are you using the current release of the PHP libraries, and the debug trace from above was a create action, not edit?

Assuming that is correct, then the number (114 above) is the primary key value of the host table (products) of the new row being created. It is attempting to create a link between the products and the descriptions tables, since a new description is being inserted.

Is that what you want to happen, a new description for every new product?

Can you show me your DB schema?

Thanks,
Allan