使用单个查询(递归查询)在 mysql 表中查找所有父项

2021-11-20 00:00:00 tree mysql

我有这个架构

示例数据

| ID |             TITLE | CONTROLLER |            METHOD | PARENT_ID |
|----|-------------------|------------|-------------------|-----------|
|  1 |         Dashboard |      admin |         dashboard |         0 |
|  2 |           Content |      admin |           content |         0 |
|  3 |           Modules |      admin |           modules |         0 |
|  4 |             Users |      admin |             users |         0 |
|  5 |          Settings |      admin |          settings |         0 |
|  6 |           Reports |      admin |           reports |         0 |
|  7 |              Help |      admin |              help |         0 |
|  8 |             Pages |    content |             pages |         2 |
|  9 |             Media |    content |             media |         2 |
| 10 |          Articles |    content |          articles |         2 |
| 11 |            Menues |    content |            menues |         2 |
| 12 |         Templates |    content |         templates |         2 |
| 13 |            Themes |    content |            themes |         2 |
| 14 |              Blog |    content |              blog |         2 |
| 15 |             Forum |    content |             forum |         2 |
| 16 |      Core Modules |    modules |       core_module |         3 |
| 17 |      User Modules |    modules |       user_module |         3 |
| 18 |         All Users |      users |         all_users |         4 |
| 19 |            Groups |      users |            groups |         4 |
| 20 |       Permissions |      users |       permissions |         4 |
| 21 | Import and Export |      users |     import_export |         4 |
| 22 |        Send Email |      users |         send_mail |         4 |
| 23 |     Login Records |      users |     login_records |         4 |
| 24 |  General Settings |   settings |  general_settings |         5 |
| 25 |    Email Settings |   settings |    email_settings |         5 |
| 26 |   Popular Content |    reports |   popular_content |         6 |
| 27 | Most Active Users |    reports | most_active_users |         6 |
| 28 |     Documentation |       help |     documentation |         7 |
| 29 |             About |       help |             about |         7 |
| 30 |          Products |   products |           product |        17 |
| 31 |        Categories | categories |          category |        17 |

SQL Fiddle 演示.我插入了一些示例数据.

SQL Fiddle demo.I have inserted some sample data.

挑战

我需要找到标题为 Categories 的记录的所有父项.如何仅通过一个查询就获得所有父母?
我的意思是我需要这个结果:

I need to find all the parents of the record where the title is Categories. How can I get all the parents with only a single query?
I mean I need this result:

期望输出

id | title | controller | method | url | parent_id ---------------------------------------------------------------- 3 | Modules | admin | modules | (NULL) | 0 17 | User Modules | modules | user_module | (NULL) | 3 31 | Categories | categories | category | (NULL) | 17

假设我想获取一个条目及其所有父项，并且我想使用 where 条件 id = 31，它应该获取以上记录.

Let suppose I want to fetch an entry with all of its parent and I want to use the where condition id = 31, it should fetch above records.

推荐答案

SELECT T2.id, T2.title,T2.controller,T2.method,T2.url FROM ( SELECT @r AS _id, (SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := 31, @l := 0) vars, menu m WHERE @r <> 0) T1 JOIN menu T2 ON T1._id = T2.id ORDER BY T1.lvl DESC;

演示

相关文章