Please see image below: To get the ID's of Mazda's parent categories I would use the following:
请见下图:为了获得马自达母公司类别的ID，我将使用以下内容:
SELECT `parent_id` FROM `product_categories` WHERE `category_id` = 8
if result is not equal to 0 repeat select statement
Is it considered bad practice to loop a SQL select statement until a condition is met? In other words; should I redesign the product categories table?
在满足条件之前循环SQL select语句是否被认为是错误的做法?换句话说;我应该重新设计产品类别表吗?
Many thanks for your advice.
非常感谢你的建议。
3 个解决方案
#1
2
Yes, in this case it may be warranted to add another field that contains the top-most parent.
是的，在这种情况下，可能需要添加另一个包含最上面父元素的字段。
This of course introduces redundancy, as the information is already available in the table, but sometimes that is an acceptable tradeoff for performance.
这当然引入了冗余，因为信息已经在表中可用了，但有时这是性能上的可接受的折衷。
You would add a field for the topsmost parent like this:
您将为最上面的父类添加一个字段:
category_id category_name parent_id top_id
------------ -------------- ---------- -------
1 CARS_ 0 1
4 smartphones 0 4
5 Japanese- 1 1
7 Lexus 5 1
8 Mazda 5 1
9 Korean 1 1
10 Toyota 5 1
That can be used for getting the topmost parent for a single item, or for getting all items that belong to the same parent, which would be even more complicated with the original layout.
这可以用于为单个项获取最顶端的父元素，或者获取属于同一父元素的所有项，这在原始布局中会更加复杂。
#2
1
Well, using this kind of query may complicate processing the results. I strongly and personally use recursive functions instead of query loops.
使用这种查询可能会使处理结果变得复杂。我强烈且个人地使用递归函数而不是查询循环。
more info : What is a RECURSIVE Function in PHP?
更多信息:PHP中的递归函数是什么?
#3
0
Try this (there are in-built functions for doing the same in PosgreSQL (with recursive query)
试试这个(在PosgreSQL中有内置函数可以做同样的事情(使用递归查询)
https://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7161 # 7161
#1
2
Yes, in this case it may be warranted to add another field that contains the top-most parent.
是的，在这种情况下，可能需要添加另一个包含最上面父元素的字段。
This of course introduces redundancy, as the information is already available in the table, but sometimes that is an acceptable tradeoff for performance.
这当然引入了冗余，因为信息已经在表中可用了，但有时这是性能上的可接受的折衷。
You would add a field for the topsmost parent like this:
您将为最上面的父类添加一个字段:
category_id category_name parent_id top_id
------------ -------------- ---------- -------
1 CARS_ 0 1
4 smartphones 0 4
5 Japanese- 1 1
7 Lexus 5 1
8 Mazda 5 1
9 Korean 1 1
10 Toyota 5 1
That can be used for getting the topmost parent for a single item, or for getting all items that belong to the same parent, which would be even more complicated with the original layout.
这可以用于为单个项获取最顶端的父元素，或者获取属于同一父元素的所有项，这在原始布局中会更加复杂。
#2
1
Well, using this kind of query may complicate processing the results. I strongly and personally use recursive functions instead of query loops.
使用这种查询可能会使处理结果变得复杂。我强烈且个人地使用递归函数而不是查询循环。
more info : What is a RECURSIVE Function in PHP?
更多信息:PHP中的递归函数是什么?
#3
0
Try this (there are in-built functions for doing the same in PosgreSQL (with recursive query)
试试这个(在PosgreSQL中有内置函数可以做同样的事情(使用递归查询)
https://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7161 # 7161