SQL query to make products appear in multiple stores

[rui_silva1]

Hey,

I have a multiple store magento 2 installation and some of the products were imported and lost the second store id. So I need a query where it's selected all the products that are in store 1 and not in store 2 and change them to appear in store 2 too.

It can be done in the backoffice by just selecting the store but doing that to something like 4000 products it might take a little while, so I would like to do a query to do this, but I can't figure how to do it properly.

I need a query that selects all products that are in website ID 1 and not in website ID 5 then make them be in website 5 too.

Thanks in advance!

[Vimal Kumar]

Hi @rui_silva1,

Website level product entries are stored in catalog_product_website database table.

There will be multiple row with product id for each website id in which they are assigned.

You can test once for single product.




Then you can export this table in csv format and add multiple rows for the products which you want to add and then re-import the csv file.

Note: take a DB table dump. 

I hope it will help you!

[rui_silva1]

Thanks @Vimal Kumar that helps, I can see all the products and which website they are into. But there is any way to just select all the products that are just in website. Like select all rows where ID are in website 1 and don't have a matching to website 5?

Idk if it's understandable.

edit:

Something like this would work? Like export all this result (removing the count) and change all website ID to 5 then import?

SELECT count(*), `product_id`, `website_id`
FROM `catalog_product_website`

WHERE `website_id` = '1'
GROUP BY `product_id`
HAVING COUNT(*) = 1;

Thanks!

[Vimal Kumar · Accepted solution]

Hi @rui_silva1

You can get the records which are assigned to only one website using below query. 

SELECT `product_id`,count(product_id) FROM `catalog_product_website` GROUP BY `product_id` HAVING COUNT(product_id) = 1 ORDER BY `catalog_product_website`.`product_id` DESC 

You can get the product which are assigned to both website using below query.

SELECT `product_id`,count(product_id) FROM `catalog_product_website` GROUP BY `product_id` HAVING COUNT(product_id) > 1 ORDER BY `catalog_product_website`.`product_id` DESC 

You can get the records which are assigned to website_id =5 only,

SELECT `product_id`,count(product_id), website_id FROM `catalog_product_website` WHERE `website_id` = '5' GROUP BY `product_id` HAVING COUNT(product_id) = 1 ORDER BY `catalog_product_website`.`product_id` DESC 

I hope it will help you!