BUG: unique fields inheritance

[kaurov]

If the class has some field marked as 'unique' in class configuration, and DataDirector has checkbox to use inheritance in dataport, then DataDirector get's error during the import: 10:57:27 ERROR [app] Object #26017 could not be saved. Reverted changes. Error: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'PA0490:z:ab83aef5dec53bad041473916469c26c' for key 'u_index_ProAlphaID' in /vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117

I believe it happens because DataDirector inheritance works that way: if some field value of all children-products are the same then this value is saved to the parent object and so inherited to all children. If the child is only on then DataDirector tries to set the same value to the parent. But for unique fields this logic should be skipped.

[BlackbitDevs]

I believe it happens because DataDirector inheritance works that way: if some field value of all children-products are the same then this value is saved to the parent object and so inherited to all children.

Are you speaking about the "Optimize inheritance" feature? This works as described in https://www.youtube.com/watch?v=B4GqswnTYtU&t=760s

But the "use inheritance" checkbox actually is not about setting data but it is about filtering and getting. When you have a Pimcore-based dataport and checked the "Use inheritance" checkbox, the result of the data query selectors will use inheritance, so for example a raw data field with data query selector name will contain the parent's name if name is empty for the current object. If "Use inheritance" in unchecked, you will get an empty string in this case.

From DD version 3.1 this concept will also get applied to the SQL condition. When you uncheck "use inheritance" then the objectstore* tables will get queried. Which means in above example with a condition of IFNULL(name, '') = '' you will only get objects which really have a value in the field name, not those which only inherited the value.

If this does not answer your question yet, please explain the problem with some example data.

[kaurov]

I believe that unique fileds import should not cause SQL error, whatever logic it does.

Btw, if to mark that unique field as 'key attribute' then import works without error. Just why should we mark a key the field that is not a key.

[BlackbitDevs]

Sorry, I cannot follow you. If you have a unique field sku in your class and you have the following data	sku	erp_id
1	12345
1	54321

In your import you use the erp_id as a key field. This of course has to regard the unique field and throw the Integrity constraint violation exception for the sku field. So there is obviously either an error in the source system or the Pimcore data model is not correct. Or what behaviour would you expect in above example? Or have I simplified it too much? Then please provide a more explaining example.

[kaurov]

sku	parentId	erp_id
1		12345
2	4	54321

Such data in the "Use inheritance" mode will try to create 3 products (3rd is autogenerated parent SKU=4 since it is not in table and there is a link on it) and will cause SQL error that product with SKU=4 cannot have the same erp_id=54321 as its child with SKU=2 (erp_id is unique)

[BlackbitDevs]

3rd is autogenerated parent SKU=4 since it is not in table and there is a link on it

You mean that in the callback function for Path you have return 'Product:external_id:'.$params['value']; with the column parentId assigned? Or how does the parent with SKU=4 get generated? And why do you want to have the auto-generated parent and the real product to have the same erp_id? Doesn't this cause problems in other imports where you use erp_id as key field? Because then all objects, which match the key fields, will get updated - so in this case the auto-generated parent and the real object.

[kaurov]

You mean that in the callback function for Path you have return 'Product:external_id:'.$params['value']; with the column parentId assigned?

yes.

And why do you want to have the auto-generated parent and the real product to have the same erp_id?

I do not want that, this is how inheritance in DataDirector currently works. I believe that unique fields should be unique and stay only in that object where it was originally defined.

[BlackbitDevs]

Still don't get it. Import data:

sku	parentId	erp_id
1		12345
2	4	54321

This import will create the following objects:

• sku=1
  • external_id=12345
  • parent object: target folder from dataport configuration
• sku: null
  • external_id=4 -> auto-generated from returned data query selector for non-existing parentId=4
  • parent object: target folder from dataport configuration
• sku=2
  • external_id=54321
  • parent object: the auto-generated object with sku=null

Correct? This would not cause a unique contraint error - even if there were multiple auto-generated items because null does not cause unique key collision errors.

[kaurov]

SKU is exactly external_id.

So resulting objects will look like:

id	SKU	parentId	erp_id	fullPath
1	SKU1	null	12345	/SKU1
2	SKU4	null	54321	/SKU4
3	SKU2	2	54321(inherited)	/SKU4/SKU2

and error msg of couse that erp_id is douplicated on 54321. I solved this problem by disabling the unique key from erp_id field.

---

Btw, if to import .xml file for SKU=4, then id#2 would be DELETED! and created with new id. Thanks, its children would be assigned to the new object, but the manually inputed data is lost. So after second import the table will look like:

id	SKU	parentId	erp_id	fullPath
1	SKU1	null	12345	/SKU1
3	SKU2	4	54321	/SKU4/SKU2
4	SKU4	null	correctValue	/SKU4

[BlackbitDevs]

Where does your second item get the erp_id from?

id	SKU	parentId	erp_id	fullPath
2	SKU4	null	54321	/SKU4

With the data query selector Product:external_id:<parentId> there will be an empty parent object being created with parentId=NULL but without erp_id.

Can you send the complete import log?

I will try to replicate this tomorrow with exactly the example data... perhaps there is a bug but conceptionally there won't be a unique constraint collision.