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
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.
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.
