CODEWITHSUNDEEP
mysqlsqlhierarchical-data
user2231688
user2231688

Reputation: 197

MySQL Count products from all subcategories

I have two tables; categories and products. For each category i would like to count how many products there are in all of its subcategories. I already have counted how many are in each category. Example tables are:

Categories:

ID  ParentID  ProductCount  SubCategoryProducts
1   NULL      0
2   1         2
3   2         1

Products:

ProductID  CategoryID
123        2
124        2
125        3

So i would like my function to make:

ID  ParentID  ProductCount  SubCategoryProducts
1   NULL      0             3
2   1         2             1
3   2         1             0

It simply needs to be as a select query, no need to update the database.

Any ideas?

EDIT: SQL FIddle: http://sqlfiddle.com/#!2/1941a/4/0

Upvotes: 0

Views: 2585

Answers (4)

Bill Karwin
Bill Karwin

Reputation: 562328

You can do this in one statement if you have a limit on the depth of the hierarchy. You said you only have 4 levels in total.

SELECT SUM(ProductCount)
FROM (
    SELECT c0.ID, c0.ProductCount
    FROM Categories AS c0
    WHERE c0.ID = 1
    UNION ALL
    SELECT c1.ID, c1.ProductCount
    FROM Categories AS c0
    JOIN Categories AS c1 ON c0.ID = c1.ParentID
    WHERE c0.ID = 1
    UNION ALL
    SELECT c2.ID, c2.ProductCount
    FROM Categories AS c0
    JOIN Categories AS c1 ON c0.ID = c1.ParentID
    JOIN Categories AS c2 ON c1.ID = c2.ParentID
    WHERE c0.ID = 1
    UNION ALL
    SELECT c3.ID, c3.ProductCount
    FROM Categories AS c0
    JOIN Categories AS c1 ON c0.ID = c1.ParentID
    JOIN Categories AS c2 ON c1.ID = c2.ParentID
    JOIN Categories AS c3 ON c2.ID = c3.ParentID
    WHERE c0.ID = 1
) AS _hier;

That'll work for this query if you store the hierarchy in the way you're doing, which is called Adjacency List. Basically, the ParentID is the way each node records its position in the hierarchy.

There are a few other ways of storing hierarchies that allow for easier querying of whole trees or subtrees. The best data organization depends on which queries you want to run.

Here are some more resources:

Upvotes: 0

Bobochil
Bobochil

Reputation: 31

This assumes you have Product table named prods

prod_id|categ_id

and Category table named categ

categ_id|parent_categ_id

As you seem to be using Adjacency List structure where foreign key parent_categ_id column references prod_id column at the same table the following query should work

select c1.categ_id,c1.parent_categ_id,count(prods.prod_id)
as product_count from categ c1
join prods on prods.categ_id=c1.categ_id or prods.categ_id
    in( with recursive tree(id,parent_id)as 
    (select categ_id,parent_categ_id from categ 
    where categ_id=c1.categ_id  
    union all
    select cat.categ_id,cat.parent_categ_id from categ cat
    join tree on tree.id=cat.parent_categ_id) select id from tree)
group by c1.categ_id,c1.parent_categ_id 
order by product_count

Upvotes: 0

Radosław Andraszyk
Radosław Andraszyk

Reputation: 450

Here is my procedure for counting products in all subcategories

DELIMITER $$
CREATE PROCEDURE CountItemsInCategories(IN tmpTable INT, IN parentId INT, IN updateId INT)
BEGIN

    DECLARE itemId INT DEFAULT NULL;
    DECLARE countItems INT DEFAULT NULL;
    DECLARE done INT DEFAULT FALSE;
    DECLARE recCount INT DEFAULT NULL;

    DECLARE 
        bufItemCategory CURSOR FOR
    SELECT
        itemCategory.id AS id,
        COUNT(CASE WHEN item.isVisible = 1 then 1 ELSE NULL END) items
    FROM
        itemCategory
    LEFT JOIN item ON
        item.categoryId = itemCategory.id 
    WHERE
        itemCategory.isVisible = 1 AND itemCategory.categoryParentId = parentId 
    GROUP BY
        itemCategory.id
    ORDER BY
        itemCategory.name;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET max_sp_recursion_depth = 10000;

    IF tmpTable = 1 THEN
        DROP TEMPORARY TABLE IF EXISTS tblResults;
        CREATE TEMPORARY TABLE IF NOT EXISTS tblResults(
            id INT NOT NULL PRIMARY KEY,
            items INT
        );
    END IF;

    OPEN bufItemCategory;

        Reading_bufItemCategory: LOOP

            FETCH FROM bufItemCategory INTO itemId, countItems;

            IF done THEN
                LEAVE Reading_bufItemCategory;
            END IF;

            IF tmpTable = 1 THEN
                INSERT INTO tblResults VALUES(itemId, countItems);
            ELSE
                UPDATE tblResults SET items = items + countItems WHERE id = updateId;
            END IF;

            SET recCount = (SELECT count(*) FROM itemCategory WHERE itemCategory.categoryParentId = itemId AND itemCategory.isVisible = 1);

            IF recCount > 0 THEN
                CALL CountItemsInCategories(0, itemId, CASE WHEN updateId = 0 then itemId ELSE updateId END);
            END IF;

        END LOOP Reading_bufItemCategory;

    CLOSE bufItemCategory;

    IF tmpTable = 1 THEN
        SELECT * FROM tblResults WHERE items > 0;
        DROP TEMPORARY TABLE IF EXISTS tblResults;
    END IF;

END $$
DELIMITER;

To call procedure just run:

CountItemsInCategories(firstLoop,parentId,updateId);

Where parameters are:

firstLoop - always "1" for first loop

parentId - parent of subcategories

updateId - id of row to update, always "0" for first loop

On example:

CountItemsInCategories(1,1,0);

I hope this example will be useful to someone.

Upvotes: 0

Joao
Joao

Reputation: 2746

If it were me I'd create a STORED PROCEDURE. The other option is to loop with PHP through the first query, then for each ID run another query - but this kind of logic can slow down your page drastically.

Here's a nice tutorial on stored procedures: http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/

Basically you run the same loops I mentioned above you would with PHP (but it runs much faster). The procedure is stored in the database and can be called like a function. The result is the same as a query.

As requested, here's a sample procedure (or rather, it uses two) in my instance, "ags_orgs" acts in a similar way to your categories where there is a parentOrgID. "getChildOrgs" also acts kind of like a redundant function since I had no idea how many levels down I had to go (this was written for MSSQL - there are probably differences with mySQL) Unfortunately this doesn't count rows, rather it gets data. I highly recommend following a tutorial or two to get a better grip on how it works:

USE [dbname]
GO

/****** Object:  StoredProcedure [dbo].[getChildOrgs]    Script Date: 09/26/2012 15:30:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[getChildOrgs]

@myParentID int,
@isActive tinyint = NULL

AS
BEGIN

    SET NOCOUNT ON
    DECLARE @orgID int, @orgName varchar(255), @level int

        DECLARE cur CURSOR LOCAL FOR SELECT orgID FROM dbo.ags_orgs WHERE parentOrgID = @myParentID AND isActive = ISNULL(@isActive, isActive) ORDER BY orderNum, orgName


    OPEN cur
        fetch next from cur into @orgID
    WHILE @@fetch_status = 0
    BEGIN
        INSERT INTO #temp_childOrgs SELECT orgID,orgName,description,parentOrgID,adminID,isActive,@@NESTLEVEL-1 AS level  FROM dbo.ags_orgs WHERE orgID = @orgID

        EXEC getChildOrgs @orgID, @isActive
        -- get next result
        fetch next from cur into @orgID
    END
    CLOSE cur
    DEALLOCATE cur

END

GO

Which is called by this proc:

USE [dbname]
GO

/****** Object:  StoredProcedure [dbo].[execGetChildOrgs]    Script Date: 09/26/2012 15:29:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[execGetChildOrgs]

@parentID int,
@isActive tinyint = NULL,
@showParent tinyint = NULL

AS

BEGIN

CREATE TABLE #temp_childOrgs
(
   orgID int,
   orgName varchar(255),
   description text,
   parentOrgID int,
   adminID int,
   isActive tinyint,
   level int
)
-- if this isn't AGS top level (0), make the first record reflect the requested organization
IF @parentID != 0 AND @showParent = 1
BEGIN
    INSERT INTO #temp_childOrgs SELECT orgID,orgName,description,parentOrgID,adminID,isActive,0 AS level  FROM dbo.ags_orgs WHERE orgID = @parentID
END

exec getChildOrgs @parentID, @isActive

SELECT * FROM #temp_childOrgs
DROP TABLE #temp_childOrgs
END

GO

Upvotes: 1

Related Questions