Permanently Delete Products from DB

1 year ago
I would like to make a suggested feature to have a button that permanently deletes products from the DB that have not been ordered. And for it to delete the pictures, attributes, SEO URL, basically everything in the Nop DB that is tied to that product...I know this isn't a HUGE suggestion, but I like to have clean tables :/ call me weird.
1 year ago
Yes!

Even products that 'have' been ordered should be able to be actually deleted (IMO). Checking to verify the 'current' existence of a product (with it's associated product info, photos, etc) could be performed, and if no 'valid current' product is located then replacement is made with generic 'product unavailable' messages (much like the check for the presence of an image or the default image is shown).

IMO, Historical orders should not 'pull' current product details to display for an order in the past... the info for the current version of a product available for purchase could vary from the historically purchase item. So, why guard & store deleted products and associated data. If the data for an order is desired to be stored, store 'only' what would have been included with an order record... with whatever variables existed for THAT singular order.

my 2 cents ;-)
6 months ago
I have this stored procedure this will delete product and all its references in the table one at a time


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- [dbo].[DeleteProductById] 123
Create PROCEDURE [dbo].[DeleteProductById1]
(
  @ProductId int
)
AS
BEGIN  
  
  CREATE TABLE #tmp_Orders (Id integer)
  CREATE TABLE #tmp_Pictuers (Id integer)
  delete    FROM ShoppingCartItem Where  ProductId = @ProductId
  insert into #tmp_Orders select OrderId from OrderItem where ProductId = @ProductId
  delete from  #tmp_Orders
  delete  from OrderItem where ProductId = @ProductId
  delete    FROM OrderNote where OrderId in (Select Id from #tmp_Orders)
  delete  from [Order] where id in (select orderID from OrderItem where productId = @ProductId)
  delete    FROM RecurringPayment where InitialOrderId in (select orderID from OrderItem where productId = @ProductId)
  delete    FROM RewardPointsHistory where UsedWithOrder_Id in  (select orderID from OrderItem where productId = @ProductId)
  delete    FROM [Order] where Id in (Select Id from #tmp_Orders)  
  delete    FROM ProductAttributeCombination Where  ProductId = @ProductId
  delete    FROM RelatedProduct where  ProductId1 = @ProductId
  delete    FROM ProductAttributeValue where AssociatedProductId = @ProductId
  delete    FROM Product_Category_Mapping where ProductId = @ProductId
  delete    FROM Product where id = @ProductId --  order by ID desc
  insert into #tmp_Pictuers select PictureId from Product_Picture_Mapping where ProductId = @ProductId
  delete    FROM Product_Picture_Mapping where ProductId = @ProductId
  delete    FROM Picture where id in (select Id from #tmp_Pictuers) --order by ID desc
  delete    FROM Product_ProductAttribute_Mapping Where  ProductId = @ProductId
  delete    FROM Product_ProductTag_Mapping Where  Product_Id = @ProductId
  delete    FROM Product_SpecificationAttribute_Mapping Where  ProductId = @ProductId
  delete    FROM ProductReviewHelpfulness where ProductReviewId in (select id   FROM ProductReview Where  ProductId = @ProductId)
  delete from UrlRecord Where EntityName = 'Product' and EntityId = @ProductId
  delete from Product where Id = @ProductId
  

  DROP TABLE #tmp_Orders
  DROP TABLE #tmp_Pictuers
END
1 month ago
I finally implemented this! Thanks!