Counting subcategories and ads in every subcategory mysql

Question

As the title say , i have two tables one for categories and other for ads

  ads table

  id  , cat_id 
   31 ,  16
   32 ,  16
   33 ,  2

  categories table

  id , tree 
  1 , 0
  2,  0
  6,  0
  13, 1
  16, 6
  17, 6
  18, 6

My wished result is

  category , num subcategories  , num ads
      1    ,   1                ,  0
      2    ,   0                ,  1
      6    ,   3                ,  2

I want just get where the tree is 0 (Main categories) .

Here is my fiddle

Answer 1

If you have only a 2-level hierarchy, then you can do it like this:

select
    y.category,
    coalesce(z.sub_category_count, 0)  as num_sub_categories,
    y.num_ads
from
    (
        select               -- combine the level-1 and level-2 ad counts by category
            x.category,
            sum(ad_count)            as num_ads
        from
            (
                select       -- count ads for level-1 categories, by category
                    a.id        as category,
                    count(b.id) as ad_count
                from
                    categories a
                    left outer join
                    ads b
                    on a.id = b.cat_id
                where
                    a.tree = 0
                group by
                    a.id
                union
                select       -- count ads for level-2 categories, by level-1 category
                    c.tree       as category,  
                    count(d.id)  as ad_count
                from
                    categories c
                    left outer join
                    ads d
                    on d.cat_id = c.id
                where
                    c.tree <> 0
                group by
                    c.tree
            ) x
            group by
                x.category 
    ) y
    left outer join
    (
        select       -- count sub_categories by category
            tree      as category,
            count(id) as sub_category_count
        from
            categories
        where
            tree <> 0
        group by 
            tree
    ) z
    on y.category = z.category
order by
    category;

Results:

+----------+--------------------+---------+
| category | num_sub_categories | num_ads |
+----------+--------------------+---------+
|        1 |                  1 |       0 |
|        2 |                  0 |       1 |
|        6 |                  3 |       2 |
+----------+--------------------+---------+
3 rows in set (0.00 sec)

If your hierarchy goes to more than 2 levels then it will get more more complicated.

Answer 2

As others said, MySQL is probably not the best choice for this task. If you want to use it anyway, here is a possible solution using Nested set model. Extend table categories with two additional integer fields, lb and rb, that will keep left and right boundary of the particular category. All subcategories of that category will need to have their interval [lb, rb] completely contained within the parent category interval. Thus, the table categories should look like:

id tree lb  rb
==============
1   0   11  14
2   0   9   10
6   0   1   8
13  1   12  13
16  6   2   3
17  6   4   5
18  6   6   7

and the query that returns your wished result is:

select id,
(select count(*) from categories where lb >= cat.lb and rb <= cat.rb and tree > 0) as num_subcategories,
(select count(*) from ads a join categories c on a.cat_id = c.id where lb >= cat.lb and rb <= cat.rb) as num_ads
from categories cat
where tree = 0;

Here is fiddle. Notice that inserts and deletions of categories would become more complicated, that is a tradeoff to get easy search through the whole hierarchy. Here is a procedure for insert:

drop procedure if exists insert_category;
create procedure insert_category(_id int, _parent_id int) 
begin
    declare _parent_rb int default null;    

    if _parent_id = 0 then
        set _parent_rb = 1;
    else
        select rb from categories where id = _parent_id
        into _parent_rb;
    end if;

    update categories set rb = rb + 2 where rb >= _parent_rb;   
    update categories set lb = lb + 2 where lb >= _parent_rb;

    insert into categories(id, tree, lb, rb) 
    values (_id, _parent_id, _parent_rb, _parent_rb + 1);
end;

To get the table categories filled as described above, just call this procedure multiple times:

call insert_category(1, 0);
call insert_category(2, 0);
call insert_category(6, 0);
call insert_category(13, 1);
call insert_category(16, 6);
call insert_category(17, 6);
call insert_category(18, 6);

HTH.