包含产品计数的相关类别和子类别

Question

Im looking to create a ul list of categories and its sub categories (with count) related to the users search.

Example. if you was to go to ebay and search mac book pro, on the left it would show

Computers/Tablets & Networking (20 items)
   -Laptop & Desktop Accessories (5 items)
   -Laptops & Netbooks (15 items)

I have 2 tables:

Categories Table

id  |  category_name  |  parent_id
--------------------------------------
1   | computers       |     0
2   | apple           |     2
3   | microsoft       |     2
4   | accessories     |     0
5   | mouse           |     4
6   | keyboards       |     4
7   | printers        |     4

#############################################################

And Products Table

id  |  product_name  |  category_id
--------------------------------------
1   | macbook pro      |     2
2   | macbook air      |     2
3   | surface pro      |     3
4   | ipad             |     2
5   | backlit keyboard |     6
6   | mini keyboard    |     6
7   | 3 in 1 printer   |     7


some sql and php to disply:

computers (4)
  -apple (3)
  -microsoft (1)

accessories (3)
  -keyboards (2)
  -printers (1)
  -(dont show mouse because no mouses in products table)

I have spent the last few hours searching but havent found what im looking for.

Regards
Shane

Answer 1

I think you can get what you want just using with rollup. The problem is that the sum will appear after the base rows:

  select cp.category_name as parent_name, c.category_name, count(*) as numcategories
  from products p join
       categories c
       on p.categoryid = c.id join
       categories cp
       on c.parentid = cp.id
  group by cp.category_name, c.category_name with rollup

To get it before, try this:

select parent_name, category_name, numcategories
from (select cp.category_name as parent_name, c.category_name, count(*) as numcategories
      from products p join
           categories c
           on p.categoryid = c.id join
           categories cp
           on c.parentid = cp.id
      group by cp.category_name, c.category_name with rollup
     ) t
where category_name is not null
order by category_name,
         category_name is null desc;