How to Find All Parent Entries in a MySQL Table Using a Single Recursive Query?-Mysql Tutorial-php.cn

How to Find All Parent Entries in a MySQL Table Using a Single Recursive Query?

DDD

Release： 2024-11-30 19:23:15

Original

199 people have browsed it

How to Find All Parent Entries in a MySQL Table Using a Single Recursive Query?

Finding All Parents in MySQL Table with Single Query (Recursive Query)

Tables such as menus or categories often have hierarchical structures where entries are related to each other via a parent-child relationship. In such scenarios, retrieving all the parents of a particular entry can become a challenge, especially when attempting to do so with a single query.

Consider the following schema containing menu entries:

| 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 |

Copy after login

The goal is to find all parents of an entry with ID 31 (Categories) using a single query. To achieve this, we can employ a recursive query:

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;

Copy after login

This query uses a common table expression (CTE) to iteratively retrieve the parents of an entry. The vars subquery initializes the variables @r and @l, which represent the current entry's ID and the level of the recursion, respectively. The main subquery, T1, then uses a recursive query to find the parent of the current entry while incrementing the level.

Finally, the T1 CTE is joined with the menu table, T2, to retrieve the details of each parent entry. The ORDER BY T1.lvl DESC clause orders the results in descending order of level, ensuring that the closest parents are shown first.

Using this recursive query, we can obtain the following desired output:

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

Copy after login

The above is the detailed content of How to Find All Parent Entries in a MySQL Table Using a Single Recursive Query?. For more information, please follow other related articles on the PHP Chinese website!