📄SQL Tips & Tricks (shopware 6)

Note: DO NOT USE IN PRODUCTION ENVIRONMENT

Delete all products

With the following query, you can delete all created products and empty the product overview completely.

delete pv.*, pt2.*, pt.*, psk.*, pr.*, pp2.*, pp.*, po.*, pm.*, pcfs.*, pcst.*, pcsap.*, pcs2.*, pcs.*, pct.*, pc.*, p.* from product p
left join product_category pc on p.id = pc.product_id 
left join product_category_tree pct on p.id = pct.product_id 
left join product_configurator_setting pcs on p.id = pcs.product_id 
left join product_cross_selling pcs2 on p.id = pcs2.product_id 
left join product_cross_selling_assigned_products pcsap on p.id = pcsap.product_id 
left join product_cross_selling_translation pcst on pcs2.id = pcst.product_cross_selling_id 
left join product_custom_field_set pcfs on p.id = pcfs.product_id 
left join product_media pm on p.id = pm.product_id 
left join product_option po on p.id = po.product_id 
left join product_price pp on p.id = pp.product_id 
left join product_property pp2 on p.id = pp2.product_id 
left join product_review pr on p.id = pr.product_id 
left join product_search_keyword psk on p.id = psk.product_id 
left join product_tag pt on p.id = pt.product_id 
left join product_translation pt2 on p.id = pt2.product_id 
left join product_visibility pv on p.id = pv.product_id;

Delete all properties

With the following query, you can remove all properties.

delete pgt.*, pgot.*, pgo.*, pg.* from property_group pg 
join property_group_option pgo on pg.id = pgo.property_group_id 
join property_group_option_translation pgot on pgo.id = pgot.property_group_option_id 
join property_group_translation pgt on pg.id = pgt.property_group_id;

Delete all orders

delete ot2.*, ot.*, oli.*, odp.*, od.*, oc.*, oa.*, o.* from `order` o 
left join  order_address oa on o.id = oa.order_id 
left join  order_customer oc on o.id = oc.order_id 
left join  order_delivery od on o.id = od.order_id 
left join  order_delivery_position odp on od.id = odp.order_delivery_id 
left join  order_line_item oli on o.id = oli.order_id 
left join  order_tag ot on o.id = ot.order_id 
left join  order_transaction ot2 on o.id = ot2.order_id;

Disable non-standard extensions

With the following queries you can deactivate the extensions that are not already present when installing Shopware. This is especially useful for debugging errors, as you can quickly eliminate the influence of extensions. If you use your own theme that is integrated via a plug-in, you should make sure in advance that the theme is not assigned to a sales channel. Alternatively, you may encounter problems when calling the appropriate sales channels. First we create a temporary backup table. The current status of the extensions is saved in this table.

CREATE TABLE plugin_tmp LIKE plugin;
INSERT INTO `plugin_tmp` SELECT * FROM `plugin`;

Then you can deactivate the extensions

UPDATE `plugin` SET `active`= 0 WHERE (author <> 'shopware AG' AND author <> 'Shopware') OR (author IS NULL);

The original state of the extensions can be restored later as follows

UPDATE plugin AS p JOIN plugin_tmp AS pt ON p.id = pt.id SET p.active = pt.active;

Finally, you can delete the temporary backup table

DROP TABLE plugin_tmp;