MySQL，可能是嵌套类别问题 [英] Mysql, Possible Nested Category problem
问题描述
我有一个mysql问题.
I have a mysql question.
我有一个应用程序，正在从Coldfusion和MSSql迁移到PHP/Mysql.
I have an application which I am migrating to PHP/Mysql from Coldfusion and MSSql.
有类别和子类别(显然是无限的子类别).
There are categories and sub-categories (with apparently unlimited sub-categories).
在给定NavigationID的情况下，获取产品表中所有记录的最佳方法是什么.
What is the best way to grab all of the records in a products table given a navigationID.
我正在处理两个表.一个是产品"表(其具有对应于--->的nav_id)，另一个是类别关系表，其中包括父/子关系.
there are two tables I am dealing with. One is the Products table (which has a nav_id corresponding to --->) and the other is a category relationship table which includes parent/child relationships.
因此本质上，我想查询两个表以生成与NavigationID以及该NavigationID的子类别相关的所有产品.
So essentially I want to query the two tables to produce all of the products associated with a navigationID and that NavigationID's sub categories.
有什么想法或方向吗?我知道这是必须要做的.我以为答案会在嵌套查询中，但无法解决这个问题.
any ideas or directions? I know this has to be done. Was thinking the answer would lie in a nested query but cannot wrap my head around it.
Nav_table-包含
Nav_table - contains
ChildNavId int(11)
ParentNavId int(11)
Products_Table-包含
Products_Table - contains
productNavID int(11) // productNavID contains the lowest level NavID
推荐答案
有类别和子类别(显然是无限的子类别).
There are categories and sub-categories (with apparently unlimited sub-categories).
如果以此为依据，那么您可以任意使用许多级别的类别嵌套，那么您就无法使用父级参考"模式模型通过单个查询来全部捕获它们.您将不得不递归地反复查询找到的每个类别的子项，或者只是继续做越来越长的自联接，直到您挑选出最嵌套的项.
If by this you many there are arbitrarily many levels of category nesting, you can't grab them all with a single query using the ‘parent reference’ schema model. You would have to repeatedly query the children of each category you found, recursively, or just keep doing longer and longer self-joins until you've picked up the most-deeply-nested items.
要对层级数据进行单查询访问，您必须主要研究构建模式的其他方法:
For single-query access to hierarchical data you will have to look into alternative ways of structuring the schema, principally:
- Nested Sets: stores a numerical ordering of a recursive walk over the tree
- Adjacency Relation: adds extra ancestor/descendent pairs to your child/parent lookup
这两种方法中的一种或两种都可能对您有用. (我通常以嵌套集结束，这样我也得到了明确的先后顺序.)
Either or both of these approaches, in one of their flavours, may work for you. (I usually end up with Nested Sets so that I get an explicit first-to-last order as well.)
这篇关于MySQL，可能是嵌套类别问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
