Categories with NO Products

Does anyone know how to create a query in NOp3.8 that shows all the categories that have no products associated with them and then to create a query to delete those categories.

Hello,

You can run these SQ: statements on your database:

Select all categories without products mapped to them:

  SELECT [Id]
      ,[Name]
      ,[Description]
      ,[CategoryTemplateId]
      ,[MetaKeywords]
      ,[MetaDescription]
      ,[MetaTitle]
      ,[ParentCategoryId]
      ,[PictureId]
      ,[PageSize]
      ,[AllowCustomersToSelectPageSize]
      ,[PageSizeOptions]
      ,[PriceRanges]
      ,[ShowOnHomePage]
      ,[IncludeInTopMenu]
      ,[SubjectToAcl]
      ,[LimitedToStores]
      ,[Published]
      ,[Deleted]
      ,[DisplayOrder]
      ,[CreatedOnUtc]
      ,[UpdatedOnUtc]
FROM [YourDatabase].[dbo].[Category]
WHERE [YourDatabase].[dbo].[Category].[Id] NOT IN (
SELECT [Id]
FROM [YourDatabase].[dbo].[Product_Category_Mapping])

Delete all categories without any products mapped to them:

DELETE FROM [YourDatabase].[dbo].[Category]
WHERE [YourDatabase].[dbo].[Category].[Id] NOT IN (
SELECT [Id]
FROM [YourDatabase].[dbo].[Product_Category_Mapping])

You should replace the "YourDatabase" with your database name.

Note: This was written for a database of nopCommerce 3.80. If you have troubles running this on another version please write here again and I will try to alter it.
Note: Always backup your database before doing work on it!

Hope that helps!

Regards,
Anton

Hello,

I have just found a mistake in my code in my previous post.

The Select in the Where clause in both the statements should select the CategoryId and not the Id. Making the right code something like this:

Select Categories without products:

  SELECT [Id]
      ,[Name]
      ,[Description]
      ,[CategoryTemplateId]
      ,[MetaKeywords]
      ,[MetaDescription]
      ,[MetaTitle]
      ,[ParentCategoryId]
      ,[PictureId]
      ,[PageSize]
      ,[AllowCustomersToSelectPageSize]
      ,[PageSizeOptions]
      ,[PriceRanges]
      ,[ShowOnHomePage]
      ,[IncludeInTopMenu]
      ,[SubjectToAcl]
      ,[LimitedToStores]
      ,[Published]
      ,[Deleted]
      ,[DisplayOrder]
      ,[CreatedOnUtc]
      ,[UpdatedOnUtc]
FROM [YourDatabase].[dbo].[Category]
WHERE [YourDatabase].[dbo].[Category].[Id] NOT IN (
SELECT [CategoryId]
FROM [YourDatabase].[dbo].[Product_Category_Mapping])

Delete Categories without products:

DELETE FROM [YourDatabase].[dbo].[Category]
WHERE [YourDatabase].[dbo].[Category].[Id] NOT IN (
SELECT [CategoryId]
FROM [YourDatabase].[dbo].[Product_Category_Mapping])

Sorry for my mistake!

Regards,
Anton

Thank You for your response but when I ran the query it would delete some parent categories that should stay.  Let me explain.  

Let's say you have a category path of MLB > Cardinals > Caps > Product-A
In the path there is a product so we don't want to delete the category MLB, Cardinals, nor Caps

But if we have a category path of MLB > Cardinals > Caps > No Product.  We should delete the categories or maybe unpublish the ones that don't have a product.  So in this case we would not see the category Caps but may still see the category MLB > Cardinals.  

In this scenario Cardinals might get unpublished as well. After thinking about it, we may not want to delete the Category since we might put a product back into the MLB > Cardinals > Caps path.   We don't want to get rid of the Category images and things like that.  

Does that make any sense?

Hello,

If that is the case you should only delete categories that do not have subcategories that do not have products.
I've altered my code a bit for that. But before I paste it here I would like to put a little disclaimer:
I am a hundred percent sure my SQL code is badly written and that there are people who can write a more elegant solution. My solution, however, works.

This code will delete all the categories that do not have products and do not have children that have products. It should not leave any "empty" categories by your definition. It will, however, leave top level categories that have children with products mapped to them.

DELETE FROM [YourDatabase].[dbo].[Category]
WHERE [YourDatabase].[dbo].[Category].[Id] NOT IN (
  SELECT [CategoryId]
  FROM [YourDatabase].[dbo].[Product_Category_Mapping]
) AND [YourDatabase].[dbo].[Category].[Id] NOT IN (
  SELECT [ParentCategoryId]
  FROM [YourDatabase].[dbo].[Category]
  WHERE [Id] IN (
    SELECT [CategoryId]
    FROM [YourDatabase].[dbo].[Product_Category_Mapping]
)) AND [Published] != 0

And here is the SELECT query. Run it before the DELETE query to check what categories you will delete:

SELECT *
FROM [YourDatabase].[dbo].[Category]
WHERE [YourDatabase].[dbo].[Category].[Id] NOT IN (
  SELECT [CategoryId]
  FROM [YourDatabase].[dbo].[Product_Category_Mapping]
) AND [YourDatabase].[dbo].[Category].[Id] NOT IN (
  SELECT [ParentCategoryId]
  FROM [YourDatabase].[dbo].[Category]
  WHERE [Id] IN (
    SELECT [CategoryId]
    FROM [YourDatabase].[dbo].[Product_Category_Mapping]
)) AND [Published] != 0

Again, you should replace "YourDatabase" with the name of your database.

Note: This code will not work for multiple nested categories without products. e.g. Category 1 > Category 2 > Category 3: Category 1 and 2 do not have products but Category 3 has. The script will delete category 1 and 2.
Hope this helps!

Regards,
Anton

I think you could simplify that and make it work for any number of levels in the category tree by changing it to look for any category that has no child products or child categories:

SELECT c.Id
      ,c.Name
      ,c.ParentCategoryId
      ,c.Published
      ,c.Deleted
  FROM Category c
       LEFT JOIN
       Product_Category_Mapping pcm
       ON pcm.CategoryId = c.Id
       LEFT JOIN
       Category c2
       ON c2.ParentCategoryId = c.Id
 WHERE pcm.Id IS NULL
       AND c2.Id IS NULL

and for the delete it would basically be the same query with the SELECT changed to DELETE:

DELETE c
  FROM Category c
       LEFT JOIN
       Product_Category_Mapping pcm
       ON pcm.CategoryId = c.Id
       LEFT JOIN
       Category c2
       ON c2.ParentCategoryId = c.Id
 WHERE pcm.Id IS NULL
       AND c2.Id IS NULL

That would delete any empty leaf node categories in the tree so you might have to run it a few times if you have empty categories that contain empty categories.

Alternatively if you wanted to leave the categories in place for future use and just unpublish them, then change it to an UPDATE and set published to 0:

UPDATE c
   SET c.Published = 0
  FROM Category c
       LEFT JOIN
       Product_Category_Mapping pcm
       ON pcm.CategoryId = c.Id
       LEFT JOIN
       Category c2
       ON c2.ParentCategoryId = c.Id
          AND c2.Published = 1
 WHERE pcm.Id IS NULL
       AND c2.Id IS NULL

Pete, I think that worked but I still see some categories that show a (0) behind them (So no products).  When I looked into the admin the child category of this shows a product associated with it but the product is unpublished.  So the category does have a product but it's unpublished.  So how would I modify this to also unpublish categories that have a product associated with them but the product is either 0 quantity or unpublished.

mayur5 wrote:
This is a slightly modified version of some SQL I use for finding empty categories which I've added the StockQuantity > 0 to:

    SELECT c.Id
          ,c.Name
          ,c.ParentCategoryId
          ,c.Published
          ,c.Deleted
      FROM Category c
     WHERE Published = 1
           AND Deleted = 0
           --Exclude categories that are parents of other published categories
           AND Id NOT IN (SELECT Category.ParentCategoryId 
                            FROM Category
                           WHERE Published = 1
                                 AND Deleted = 0)
           --Exclude categories that are parents of published products
           AND Id NOT IN (SELECT CategoryId
                            FROM Product_Category_Mapping
                                 JOIN
                                 Product
                                 ON ProductId = Product.Id
                                    AND Product.Published = 1
                                    AND Product.Deleted = 0
                                    AND Product.StockQuantity > 0)

You might need to tweak it to your requirements, particularly if you have any more complicated product/stock setup like grouped products or stock managed by product attributes or multiple warehouses, etc.