Permanently Delete Products from DB

Posted: February 19, 2018 at 2:05 PM Quote #200874
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.
This post/answer is useful
3
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Custom T-Shirts at http://www.ochapparel.com
Posted: February 23, 2018 at 2:56 PM Quote #201114
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 ;-)
This post/answer is useful
1
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Posted: July 11, 2019 at 2:51 AM Quote #242396
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
This post/answer is useful
1
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Premium support services
  • Dedicated premium support services provided by core developers are intended for persons who run mission critical websites, work on projects with tight deadlines, or want to get dedicated support.
Professional services
  • Want to open a new store? Want to take your store to the next level? Need a custom extension? We can customize nopCommerce to fit your store perfectly. Request a quote to get started.
eCommerce CONFERENCE 2019
Learn more