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

fvntly

I can confirm I'm using the latest version of PHP libraries.
Yes the debug trace refers to a 'create' action, which causes the SQL error. The edit action works fine, and even if the record on descriptions doesn't exist, it just gets created.
The 114 value that it is trying to insert is not the primary key of products, since that one is a 12 char alphanumeric. It's a sequential number that I don't know where it's coming from (I may now know it, more on that below). Just to try I rebooted the PHP server and that didn't restart the counter (every new create action shows a +1 higher value).
That's exactly what I'd want: on the creation of a product record, I'd like to have the description record added too.

The DB schema is:

CREATE TABLE IF NOT EXISTS 'products' (
  'order' smallint NOT NULL AUTO_INCREMENT,
  'code' char(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  'name' varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  'price' decimal(10,2) DEFAULT '0.00',
  PRIMARY KEY ('code') USING BTREE,
  UNIQUE KEY 'name' ('name') USING BTREE,
  KEY 'order' ('order'),
) ENGINE=InnoDB AUTO_INCREMENT=130 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS 'descriptions' (
  'code' char(12) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  'desc_it' varchar(1500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  'desc_en' varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY ('code'),
  CONSTRAINT 'FK_descriptions_products' FOREIGN KEY ('code') REFERENCES 'products' ('code') ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Actually... Exporting the DB schema for this response I noticed that DT is trying to set the value from the AUTO_INCREMENT of order as the primary key on descriptions, which should be 'code'.
As you can see from the PHP code on my previous response, the 'order' field is never called and so I don't understand why DT libraries are using that value.

allan

I've just recreated the issue here, based on the information provided - many thanks for that. Yes, what is happening is that the primary key value from the products table is being used and trying to insert that into the descriptions as the link value.

The Editor PHP libraries are doing that because they use the PDO lastInsertId() method to get the value from the database. i.e. regardless of what the third parameter of the Editor() constructor is set as, it will always get the value of what is actually the primary key in the database.

I think that justifiably should be considered a bug in the Editor PHP MySQL implementation and I will look into options to address that, however, I doubt that will be a quick fix - it might have far reaching consequences and I don't want to just dive into that!

In the short term, there are a couple options:

• Change your primary key for products to be code, or
• Change the linking column to be the primary key value.

Sorry I don't have an immediate and fast fix for this.

Allan

allan

I've just had a brief look, and to make this work I'd need to be able to have the query return the value, but there isn't a mechanism for that in MySQL. Postgres has RETURNING, SQL Server has OUTPUT, but I can't see a way to do it in MySQL.

Without that ability I'd need to query the newly created row to get the data, which isn't ideal. I'd probably need to add some kind of switch in there to enable such behaviour.

Based on that, I think it might be best to write to the descriptions table directly, which can be done using the writeCreate method (if you can't use either of the two options above):

Editor::inst( $db, 'products', 'code' )
    ->idPrefix( '' )
    ->fields(
        Field::inst( 'products.code' ),
        Field::inst( 'descriptions.desc_it' )
            ->set(false)
            ->getFormatter( function ( $val, $data ) {
                    return is_null($val)
                        ? ''
                        : $val;
                } )
    )
    ->on('writeCreate', function ($editor, $id, $values) {
        $desc = $values['descriptions']['desc_it'];

        $editor->db()->insert('descriptions', [
            'code' => $values['products']['code'],
            'desc_it' => $desc === '' ? null : $desc
        ]);
    })
    ->leftJoin( 'descriptions', 'descriptions.code', '=', 'products.code' )
    ->debug(true)
    ->process( $_POST )
    ->json();

Allan

fvntly

Many thanks for your time Allan.

Actually, the incremental ID that it is using to link descriptions comes from order field, which is a KEY (since it is AUTO_INCREMENT) but not the PRIMARY KEY, since that one is code (the 12 char string). So I think the issue is the library always gets the incremental ID (if there's one, I guess) instead of the primary key declared in the object constructor.
So I believe the solutions you suggested are already there: products primary key is already code and the linking value on descriptions is already its primary key.

Am I missing something? Thank you again!