Answers

• allan Posts: 63,498Questions: 1Answers: 10,471 Site admin

  September 18

  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 Posts: 4Questions: 1Answers: 0

  September 18 edited September 18

  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 Posts: 63,498Questions: 1Answers: 10,471 Site admin

  September 18

  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 Posts: 4Questions: 1Answers: 0

  September 25

  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 Posts: 63,498Questions: 1Answers: 10,471 Site admin

  September 26

  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 Posts: 63,498Questions: 1Answers: 10,471 Site admin

  September 26 edited September 26

  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 Posts: 4Questions: 1Answers: 0

  September 27

  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!