Problem I came across:
While migrating data from magento 1 to magento 2, there were some widgets for which product mapping was done. While migrating, the CMS blocks were moved along with products. However, the issue arose as the product ID in Magento 2 was different from Magento 1 and hence the rules to show CMS blocks for widgets did not work on product pages.
Solution Derived: To solve the issue, I created a script that assign widgets to products migrated from 1 database to another. The products were already migrated and hence this script helped to display widgets appropriately.
Here is the Code:
DELIMITER |
CREATE PROCEDURE explode( pDelim VARCHAR(32), pStr TEXT)
BEGIN
DROP TABLE IF EXISTS temp_explode;
CREATE TEMPORARY TABLE temp_explode (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, word VARCHAR(40));
SET @sql := CONCAT('INSERT INTO temp_explode (word) VALUES (', REPLACE(QUOTE(pStr), pDelim, '\'), (\''), ')');
PREPARE myStmt FROM @sql;
EXECUTE myStmt;
END |
DELIMITER ;
SET @str = (SELECT entities FROM m1db.`widget_instance_page` where `instance_id`=4 and page_group='configurable_products' and page_for='specific');
SET @delim = ",";
CALL explode(@delim,@str);
UPDATE m2db.`widget_instance_page` SET `entities`=(select GROUP_CONCAT(entity_id) from m2db.catalog_product_entity where sku in (select sku from m1db.catalog_product_entity where entity_id in (SELECT word FROM temp_explode)
)) WHERE `instance_id`=24 and page_group='configurable_products' and page_for='specific';
It is important to note here that I had already migrated CMS tables from Magento 1 to Magento 2 and now needed to remap product IDs.
In this code I created a PROCEDURE named ‘explode’ which is similar to explode method of php. I used this PROCEDURE to explode products ids which is stored in table ‘widget_instance_page’ as comma separated. And storing those result in a TEMPORARY table so that I can use that into where clause.
Then I got SKUs from M1 db based on product IDs I had parsed and again getting new product IDs from M2 tables from these skus and updating them corresponding to given instance_id and page_group;
Hope it helps! If you face any problems while implementing, feel free to ask by leaving a comment below.