嵌套集模型，对类别中的项目进行计数 [英] Nested Set Model, count items in categories

问题描述

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

I've got a Nested Set Model working for my site with items in subcategories and so on. It's working great except for one problem I can't come around.

+---------+-----------------------------+
| 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.我的查询将如何实现这一点? :)

In other words, all fields that don't have products in them won't be shown. Now to the problem, I want to show them with the COUNT() result = 0. How would my query look to make that happen? :)

推荐答案

听起来像是我向LEFT OUTER JOIN的任务，像这样:

Sounds like a task for LEFT OUTER JOIN to me, like so:

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

Thereby, you ensure that all categories are shown. Note that I'm not 100% sure.

为深度添加了子选择，请尝试一下.

Added sub-select for depth, give it a try.

删除了逗号

这篇关于嵌套集模型，对类别中的项目进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！