0

I am writing a module which imports additional product information into a custom table with SKU as the PK.

If there is already a row in the custom table regarding the SKU, i have to delete the old row first, otherwise an Error "Unique Constraint violation found" is thrown.

However, when i call ResourceModel->delete(), nothing seems to happen on my custom table. I can see in the Model object regarding that SKU, that the isDeleted flag is now true and storedData is now length 0. Then i tried ResourceModel->save(), got a new empty Model object from the Factory, add the new Data for the SKU to it and save() using ResourceModel or transaction->save(). This only prevented the error,but the row in the custom table stayed the same.

How do I delete from my custom table in the right way?

This is the current code with a workaround, which is not using the ResourceModel to delete:

$stockImportModel = $this->stockImportFactory->create();
                                                $this->stockImportResourceModel->load($stockImportModel, $sku);
                                                if(!$stockImportModel->isEmpty()) {
//                                                    Delete with Model/ResourceModel not working. Delete condition in ObjectRelationProcessor empty.
//                                                    $this->stockImportResourceModel->delete($stockImportModel);
                                                    $connection  = $this->resourceConnection->getConnection();
                                                    $tableName = $connection->getTableName(self::SAP_INVENTORY_TABLE);
                                                    $whereConditions = [
                                                        $connection->quoteInto('SKU = ?', $sku),
                                                    ];
                                                    $connection->delete($tableName, $whereConditions);
                                                }
                                                $stockImportModel = $this->stockImportFactory->create();
                                                $stockImportModel->addData([

ResourceModel:

 protected function _construct() {
        $this->_init('Vendor\Module\Model\ResourceModel\ResourceModelName');
    }

Model:

    protected function _construct() {
        $this->_init('catalog_product_advanced_information', 'sku');
        $this->_isPkAutoIncrement = false;
    }
2
  • why do you have the product SKU as PK and not use a a regular auto_increment column?
    – Diana
    Feb 15, 2021 at 16:23
  • I wanted a table with sku as pk, so that it is unique and i can get the products by sku with the model. I am a beginner so that approach might also be wrong :).
    – Max
    Feb 16, 2021 at 9:59

1 Answer 1

0

Sidenote on using PKs - I suggest you don't use the SKU as PK. If you want the table to refer products, you can easily use the product entity_id as FK with the proper constraints for (at least) delete like 'cascade', which will ensure that when a product gets deleted, the row from your custom table also gets deleted. Plus, you can surely retrieve the product entity_id if you know its SKU.

Although the SKU is a unique value, a simple unique integer ID column will do better and you will avoid the 'Unique Constraint violation found' error. Also Mysql works very well with integer keys so it will fetch/ update the data faster ;) You can also set the ID column to use auto_increment (in Magento this is 'identity'=true) this will guarantee keys in a sequential order, adding +1 at each new row.

What to do now? I would recommend you first rewrite your custom table and use the proper PK, and FK constraints. Then, I recommend you give it a chance to understand the basic concepts of working with factories, repositories and resource models in Magento and then put them to practice. I have a hunch that if you try to do this the right way, you won't need to delete your rows when an update occurs :) .

Here are some resources which I suggest you go through and I think they might help you with this:

https://devdocs.magento.com/guides/v2.4/extension-dev-guide/declarative-schema/db-schema.html https://devdocs.magento.com/guides/v2.4/extension-dev-guide/service-contracts/service-contracts.html https://devdocs.magento.com/guides/v2.4/extension-dev-guide/factories.html https://devdocs.magento.com/guides/v2.4/extension-dev-guide/searching-with-repositories.html https://alanstorm.com/magento_2_understanding_object_repositories/

3
  • thanks, that is really helpful!
    – Max
    Feb 17, 2021 at 22:12
  • you are welcome, @Max, feel free to upvote or mark as solved the above answer so that other people see it too.
    – Diana
    Feb 18, 2021 at 10:23
  • Sure, did both. Others can only see it when my reputation got to 15+ though.
    – Max
    Feb 18, 2021 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.