If you have an e-commerce site using Umbraco Commerce that handles a lot of traffic, you might notice an accumulation of carts that never convert into orders. This was the case for my site, which has been running for about two years and had thousands of stale carts!
Adding to the complexity, I migrated this site from Vendr to Umbraco Commerce. If you’re considering a similar migration, you can refer to the official documentation.
Post-migration, a background job was set up to export all orders to a CRM. However, the stale carts were causing issues unrelated to the migration but due to other development factors. Our definition of product changed quite a bit, causing lots of errors being thrown due to 'cart' no longer being able to identify 'product' anymore. It would have also meant customers would have 'old' products in their cart that they could no longer order, therefore, clearing them all out was the best approach.
Some people suggested deleting them manually through the CMS, selecting 30 at a time, which would have been very time-consuming.
After extensive research, I found this issue and decided to swap Vendr tables with Umbraco Commerce tables. Here’s the SQL query I used to delete the stale carts efficiently:
BEGIN TRANSACTION; -- Select all carts before date SELECT * INTO #tempOrders FROM umbracoCommerceOrder WHERE createDate < 'yyyy/mm/dd' AND (finalizedDate IS NULL) -- Delete data referenced to orders above in related tables DELETE FROM umbracoCommerceFrozenPrice WHERE umbracoCommerceFrozenPrice.OrderId IN (SELECT Id FROM #tempOrders); DELETE FROM umbracoCommerceOrderPriceAdjustment WHERE umbracoCommerceOrderPriceAdjustment.OrderId IN (SELECT Id FROM #tempOrders); DELETE FROM umbracoCommerceOrderAmountAdjustment WHERE umbracoCommerceOrderAmountAdjustment.OrderId IN (SELECT Id FROM #tempOrders); DELETE FROM umbracoCommerceOrderAppliedDiscountCode WHERE umbracoCommerceOrderAppliedDiscountCode.OrderId IN (SELECT Id FROM #tempOrders); DELETE FROM umbracoCommerceOrderAppliedGiftCard WHERE umbracoCommerceOrderAppliedGiftCard.OrderId IN (SELECT Id FROM #tempOrders); DELETE FROM umbracoCommerceOrderLineAttribute WHERE umbracoCommerceOrderLineAttribute.OrderLineId IN (SELECT Id FROM umbracoCommerceOrderLine WHERE OrderId IN (SELECT Id FROM #tempOrders)); DELETE FROM umbracoCommerceOrderLineProperty WHERE umbracoCommerceOrderLineProperty.OrderLineId IN (SELECT Id FROM umbracoCommerceOrderLine WHERE OrderId IN (SELECT Id FROM #tempOrders)); DELETE FROM umbracoCommerceOrderProperty WHERE umbracoCommerceOrderProperty.OrderId IN (SELECT Id FROM #tempOrders); DELETE FROM umbracoCommerceOrderLine WHERE umbracoCommerceOrderLine.OrderId IN (SELECT Id FROM #tempOrders) AND umbracoCommerceOrderLine.parentOrderLineId IS NOT NULL; DELETE FROM umbracoCommerceOrderLine WHERE umbracoCommerceOrderLine.OrderId IN (SELECT Id FROM #tempOrders); DELETE FROM umbracoCommerceOrder WHERE umbracoCommerceOrder.Id IN (SELECT Id FROM #tempOrders); -- Drop the temporary table DROP TABLE #tempOrders; -- Use ROLLBACK for testing -- ROLLBACK; -- Use COMMIT for deleting COMMIT; This approach allowed me to clean up the stale carts quickly and efficiently.
I hope this helps!
B
Top comments (0)