获取类别及其子项的所有项目 [英] get all items of category and its child

问题描述

我将为这个问题的解答提供100点奖励积分

因此，我对递归有一个非常棘手的问题-如何获取类别的所有项目计数以及包含该父级的所有子级的计数，以及到最后为止的更深层次?

So I have very difficult question about recursions - how to get all items count of category and all childs that contains that parent and more deeper until the end?

我有桌子:

+----+---------------+-----------------+
| id | category name | category_parent |
+----+---------------+-----------------+
| 1  | cars          |        0        |
+----+---------------+-----------------+
| 2  | real estate   |        0        |
+----+---------------+-----------------+
| 3  | clothes       |        0        |
+----+---------------+-----------------+
| 4  | bmw           |        1        |
+----+---------------+-----------------+
| 5  | audi          |        1        |
+----+---------------+-----------------+
| 6  | 100           |        5        |
+----+---------------+-----------------+
| 7  | 80            |        5        |
+----+---------------+-----------------+
| 8  | A4            |        5        |
+----+---------------+-----------------+
| 9  | QUATRO        |        8        |
+----+---------------+-----------------+
| 10 | TDI           |        8        |
+----+---------------+-----------------+
| 11 | Black         |        9        |
+----+---------------+-----------------+
| 12 | White         |        9        |
+----+---------------+-----------------+
| 13 | 2 doors       |        11       |
+----+---------------+-----------------+
| 14 | 5 doors       |        11       |
+----+---------------+-----------------+

和我的产品表如下:

+----+---------------+-----------------+
| id | category_id   | name            |
+----+---------------+-----------------+

，例如，我想计算cars类别中的所有项目.因此，基本上我应该通过此类别ID (1)并以某种方式递归计算所有项目.但是我不知道该如何处理，因为该类别的孩子可能是无限的.

and for example I want to count all items that are in cars category. So basically I should pass this category id (1) and somehow make a recursion to count all items. But I have no idea how to deal with it, becouse childs of that category could be unlimited.

因此，当我想知道该上级计数的所有项目时，我应该做这样的事情:

So when I want to know all items of that parent count, I should make something like this:

1++:
  4++
  5++:
    6++
    7++
    8++:
      9++:
        11++:
           13++
           14++
        12++
      10++

希望您能理解我的需求，并给我任何可以帮助我的建议.

I hope you will understand what I need and give me any suggestion that could help me.

这也是我到目前为止所做的开始-我可以实现它，但是将来我会陷入递归中...因此它一文不值.

also, this is the begining I have made so far - I could implement it but in the future I will stuck in recursion... so its worth nothing.

public function get_category_tree_id_list($cat_id, $list_array = FALSE)
{
    if ( !$list_array ){
        $items = $this->system->_getCustomTableData('categories', array(array('category_parent' => $cat_id)), 'id DESC');
        $this->__tmp['id_list'] = [];
        foreach ( $items as $key => $value ) {
            $this->__tmp['id_list'][] = $value['id'];
        }
    }        
}

推荐答案

您很可能希望创建嵌套集.设置起来有些棘手，但是使查询更简单.因此，您将拥有两列-lft和rgt，而不是父类别.左右基本上是类别的边界，如果项目的类别ID在这些值之间，则您知道它是该类别的子级.

You would most likely want to do nested sets. They are a little tricky to get set up, but make the queries MUCH simpler. So, instead of category parent, you are going to have two columns - lft and rgt. Left and right are basically the boundaries of a category, if an item's category id is between those values, you know that it is a child of that category.

+----+---------------+-----+------+
| id | category name | lft | rgt  |
+----+---------------+-----+------+
| 1  | cars          |  1  |  24  |
+----+---------------+-----+------+
| 2  | bmw           |  2  |  3   |
+----+---------------+-----+------+
| 5  | audi          |  4  | 23   |
+----+---------------+-----+------+
| 6  | 100           |  5  |  6   |
+----+---------------+-----+------+
| 7  | 80            |  7  |  8   |
+----+---------------+-----+------+
| 8  | A4            |  9  | 22   |
+----+---------------+-----+------+
| 9  | TDI           |  10 | 11   |
+----+---------------+-----+------+
| 10 | Quatro        |  12 | 21   |
+----+---------------+-----+------+
| 11 | Black         |  13 | 18   |
+----+---------------+-----+------+
| 12 | White         |  19 |  20  |
+----+---------------+-----+------+
| 13 | 2 doors       |  14 |  15  |
+----+---------------+-----+------+
| 14 | 5 doors       |  16 | 17   |
+----+---------------+-----+------+

然后，要获取汽车"类别中的项目数，您可以像下面这样简单地做到这一点:

Then, to get the number of items in the cars category, you can do it super simply like this:

SELECT categories.name, items.id, items.category_id, items.name 
FROM categories 
LEFT JOIN items 
    ON (items.category_id BETWEEN categories.lft AND categories.rgt)
WHERE categories.category_name = 'cars'

很明显，您可以更改category_name的值并获取ANY类别中的项目.

Obviously you can just change the value of category_name and get the items in ANY category.

抱歉，由于某种原因，当我将图像上传到此处时，图像旋转了，但是如果将类别绘制为圆形，然后对行编号，则可以看到左右值应该是什么.

Sorry, for some reason the image rotated when I uploaded it here, but if you draw out your categories as circles, and then number the lines, you can see what the value should be for left and right.

我只做过汽车，因为我认为您可以推断出其他类别.

I only did cars since I figured you could extrapolate to get the other categories.

因此，如果您这样写出您的类别:

So if you write out your categories like this:

Cars(BMW(), Audi(100(),80(),A4(TDI(),Quatro(Black(2dr(),5dr()), White())))

然后您可以用数字标记括号:

Then you can label your parenthesis with numbers:

Cars[1]->(BMW[2]->()<-[3], Audi[4]->(100[5]->()<-[6],80[7]->()<-[8],A4[9]->(TDI[10]->()<-[11],Quatro[12]->(Black[13]->(2dr[14]->()<-[15], 5dr[16]->()<-[17])<-[18], White[19]->()<-[20])<-[21])<-[22])<-[23])<-[24]

或者，如果将其绘制为树状图，则可以像这样标记它，在其中用数字标记最左边的节点，并且仅在标记了所有子节点后才标记右边的节点:

Or if you chart it out as a tree, you can label it like this, where you label the left most node with a number, and only label the right node when you have labeled all of it's children:

这篇关于获取类别及其子项的所有项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！