Permanently Delete Products from DB

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.

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 ;-)

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

I finally implemented this! Thanks!

MrTechno
What versions of Nop is this valid for? RewardPointsHistory delete makes it not valid on 4.2

geoffk wrote:

RewardPointsHistory is a valid table in 4.20.   What error are you getting?
In any case, you should query to find all tables with a ProductId field to be sure to also delete those linked records.

The column UsedWithOrder_Id isn't in RewardPointsHistory table:
delete    FROM RewardPointsHistory where UsedWithOrder_Id in  (select orderID from OrderItem where productId = @ProductId)

No it's no longer there.  It's just best to use the general idea of what the above script is doing, yet find the columns/tables related to products for your version.  However, you should verify the accuracy of the script, because it does not seem completely right to me - e.g.

  delete from  #tmp_Orders
  delete  from OrderItem where ProductId = @ProductId
  delete    FROM OrderNote where OrderId in (Select Id from #tmp_Orders)