在分层MySQL查询中需要帮助 [英] Need Help with Hierarchical Mysql Query

问题描述

我正在努力查询具有分层类别数据(用于cms)的表，该表也与我的posts数据和与post2cat表的多对多类型关系相关联.具体来说，我的问题是如何获取属于特定类别ID的所有子类别的所有帖子(不限于直接后代，但深度可以为n级)?这是我的桌子:

I'm trying to wrap my head around querying a table that has hierarchical category data (used for a cms) which is also tied to my posts data and a many-to-many type relationship with my post2cat table. Specifically, my question is how can I get all posts that belong to any of the subcategories (not limited to direct descendant but can be n levels deep) of a specific category id? Here are my tables:

类别"表:

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(45) | YES  |     | NULL    |                |
| parent_id | int(11)     | YES  | MUL | 0       |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

"post2cat"表:

'post2cat' table:

+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| post_id | int(11) | NO   | MUL | NULL    |       |
| cat_id  | int(11) | NO   | MUL | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

帖子"表:

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| title         | varchar(256) | NO   |     | NULL    |                |
| content       | text         | NO   |     | NULL    |                |
| end_date      | datetime     | NO   |     | NULL    |                |
| format_id     | int(11)      | NO   |     | NULL    |                |
| featured      | int(1)       | NO   |     | NULL    |                |
| status        | int(3)       | NO   |     | NULL    |                |
| publish_date  | datetime     | NO   |     | NULL    |                |
| date_created  | datetime     | NO   |     | NULL    |                |
| date_modified | datetime     | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

post_id名称说明

post_id name description

post2cat

推荐答案

在MySQL中仅使用SQL将树提取到任意深度 n 是一个挑战. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/"rel =" nofollow>在MySQL中管理分层数据和

It is a challenge to fetch the tree to an arbitrary depth n using just SQL in MySQL (See: Managing Hierarchical Data in MySQL and Hierarchical Queries in MySQL).

如果要处理小的数据集，则最有意义的是从数据库中选择所有类别，然后以脚本语言构建树.然后可以遍历树以找到父节点和父节点的子节点，然后可以使用联接查询数据库中的帖子.

If you are dealing with a small dataset, it makes the most sense to select all of the categories from the database and then build the tree in a scripting language. Then the tree can be traversed to find parent node and the parent node's children, which can then be used to query the database for the posts using a join.

这篇关于在分层MySQL查询中需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！