MySQL分层存储:搜索所有父/祖父母/等.给定一个子节点ID的节点? [英] MySQL hierarchical storage: searching through all parent/grandparent/etc. nodes given a child node id?
问题描述
我正在使用类似这样的分层模型来存储类别:
CATEGORIES
id | parent_id | name
---------------------
1 | 0 | Cars
2 | 0 | Planes
3 | 1 | Hatchbacks
4 | 1 | Convertibles
5 | 2 | Jets
6 | 3 | Peugeot
7 | 3 | BMW
8 | 6 | 206
9 | 6 | 306
然后,我使用以下类别ID之一存储实际数据,如下所示:
CARS
vehicle_id | category_id | name
-------------------------------
1 | 8 | Really fast silver Peugeot 206
2 | 9 | Really fast silver Peugeot 306
3 | 5 | Really fast Boeing 747
4 | 3 | Another Peugeot but only in Hatchbacks category
搜索任何此类数据时,我想找到所有子代/孙代/曾孙代等节点.因此,如果某人希望看到所有汽车",那么他们会看到parent_id为掀背车"的所有事物,以及parent_id为标致"的所有事物,依此类推,直至任意级别.
因此,如果我列出的真正快的标致206"的category_id为1、3、6或8,则我的查询应该能够遍历"树并找到父母/祖父母或父母的其他更高类别.该子类别的例如.搜索类别"8"中的标致的用户应该找到列出类别6、3或1的所有标致-所有类别8的后代.
例如使用上述数据,在类别3中搜索标致"实际上应该找到车辆1、2和4,因为车辆1和2具有类别祖先路径,该路径可以追溯到类别3.
很抱歉,如果我没有很好地解释这一点.它很难!谢谢你.
注意:我已阅读有关层次结构的MySQL开发文章
本文可以帮助您
So if I list a "really fast Peugeot 206" with a category_id of either 1, 3, 6, or 8, my query should be able to "travel up" the tree and find any higher categories which are parents/grandparents of that child category. E.g. a user searching for Peugeots in category "8" should find any Peugeots listed with categories 6, 3, or 1 - all of which category 8's descendants. E.g. using the above data, searching for "Peugeot" in category 3 should actually find vehicles 1, 2 and 4, because vehicles 1 and 2 have a category ancestor trail which leads back up to category 3. See? Sorry if I haven't explained this well. It's difficult! Thank you, though. Note: I have read the MySQL dev article on hierarchies. This article can help you http://www.phpro.org/tutorials/Managing-Hierarchical-Data-with-PHP-and-MySQL.html 这篇关于MySQL分层存储:搜索所有父/祖父母/等.给定一个子节点ID的节点?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!