SQL - Count on recursive category

Question

I'm stucked into a count query.

I have 3 tables:

Articoli
ID | Title | ecc...

Categories
ID | Name | Parent

articles_category
category_id | article_id

Category are recursive, for example I have a main category "News" with 3 sub cat.

I need to count how many article are in "News", but my article are tagged in the "articles_category" table with the subcat ID (if there is a SubCat) or with main Cat ID, if it have no subcat. So far i tried:

SELECT count(a.id), child.name AS child, parent.name AS parent
FROM categories parent
JOIN categories child ON child.parent = parent.tid
JOIN categories_articoli ca ON child.tid = ca.category_id
   OR parent.tid = ca.category_id
JOIN articoli a ON a.id = ca.articolo_id
GROUP BY parent.tid

But this return me only the parent cat that have subcategory, but this is everytime true. Any suggestion?

Answer 1

You need to use recursive sql on Categories table.

Try this:

Count(*) of articles in News category:

with category_tree(id) as
 (select  c.id
    from Categories c
   where c.name='News'--category tree starting with 'News'
  union all
  select  c.id
    from category_tree ct
   inner join Categories c
      on c.parent = ct.id)
select  count(distinct ac.article_id) from category_tree ct
inner join articles_category ac on ac.category_id = ct.id

Count(*) of articles by category:

with category_tree(id, root_category_name) as
 (select c.id,  c.name
    from Categories c
   where c.parent is null
  union all
  select  c.id,  ct.root_category_name
    from category_tree ct
   inner join Categories c
      on c.parent = ct.id)
select ct.root_category_name, count(distinct ac.article_id) from category_tree ct
inner join articles_category ac on ac.category_id = ct.id
group by ct.root_category_name

http://sqlfiddle.com/#!4/d42aa/12

Answer 2

Thanks a lot!

Unlucky I can't use the "WITH" statement in mysql (sorry I didn't specify this), but i solve my issue in this way:

• create a dataset in wich every ID is associated with his parent_category name
• join it on the categories_articoli table
• group by parent_category name.

Here is the query if someone may need something like this:

SELECT count(distinct ca.articolo_id), cat.name 
FROM categories_articoli ca
JOIN(
    SELECT c.tid AS ID, c.name AS name, c.parent
    FROM categories c
    WHERE c.parent = 0 AND c.tid <> 6
    UNION
    SELECT c.tid AS ID, parent.name AS name, c.parent
    FROM categories c
    JOIN categories parent ON c.parent = parent.tid
    WHERE c.parent <> 0 AND c.tid <> 10
) cat
ON cat.ID = ca.category_id
GROUP BY cat.name

Answer 3

i think that it is wrong, becouse your solution dont write "top" categories (fe.: you have cat number 3 in 2 in 1 and items only in category 3 - your solution will return right count of items in category 3 and 2, but category 1 wont be in result and it should be there)