嵌套集模型，对类别中的项目进行计数

Question

我有一个适用于我的网站的嵌套集模型，其中包含子类别中的项目，依此类推。除一个我无法解决的问题外，它的工作效果非常好。

+---------+-----------------------------+
| item_id | item_name                   |
+---------+-----------------------------+
|       1 | Laptop                      |
|       2 | iPod Classic 80GB           |
|       3 | iPod Classic 160GB          |
+---------+-----------------------------+
+---------+-------------+
| item_id | category_id |
+---------+-------------+
|       1 |           4 |
|       2 |           2 |
|       3 |           2 |
+---------+-------------+
+-------------+--------------------+-----+-----+
| category_id | name               | lft | rgt |
+-------------+--------------------+-----+-----+
|           1 | iPod               |   1 |   6 |
|           2 | Classic            |   2 |   3 |
|           3 | Nano               |   4 |   5 |
|           4 | Computers          |   7 |   8 |
+-------------+--------------------+-----+-----+

使用以下查询：

SELECT parent.name, COUNT(product.item_id)
  FROM Category AS node, Category AS parent, Item_Category AS product
  WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.category_id = product.category_id
  GROUP BY parent.name
  ORDER BY node.lft;

提供以下输出：

+-----------------+------------------------+
| name            | COUNT(product.item_id) |
+-----------------+------------------------+
| iPod            |                      2 |
| Classic         |                      2 |
| Computers       |                      1 |
+-----------------+------------------------+

换句话说，不会显示其中没有产品的所有字段。现在到问题了，我想向他们展示COUNT（）结果=0。我的查询将如何实现这一点？:)

Answer 1

听起来像是LEFT OUTER JOIN的一项任务，例如：

SELECT parent.name, COUNT(product.item_id), 
       (select count(*) from Category parent2 
         where parent.lft > parent2.lft
           and parent.rgt < parent2.rgt) as depth
  FROM Category parent
  LEFT OUTER JOIN Category node 
    ON node.lft BETWEEN parent.lft AND parent.rgt
  LEFT OUTER JOIN Item_Category product
    ON node.category_id = product.category_id
 GROUP BY parent.name
 ORDER by node.lft

因此，您确保显示所有类别。请注意，我不确定100％。

编辑：为深度添加了子选择，请尝试一下。

编辑：删除逗号