単一の再帰クエリを使用して MySQL テーブル内のレコードのすべての祖先を検索する方法

| ID |             TITLE | CONTROLLER |            METHOD | PARENT_ID |
|----|-------------------|------------|-------------------|-----------|
|  1 |         Dashboard |      admin |         dashboard |         0 |
|  2 |           Content |      admin |           content |         0 |
|  3 |           Modules |      admin |           modules |         0 |
...

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       

WITH RECURSIVE Parents AS (
    SELECT id, parent_id
    FROM menu
    WHERE id = 31
    UNION ALL
    SELECT m.id, m.parent_id
    FROM Parents AS p
    JOIN menu AS m ON p.parent_id = m.id
)
SELECT m.id, m.title, m.controller, m.method, m.url, m.parent_id
FROM Parents AS p
JOIN menu AS m ON p.id = m.id
ORDER BY p.id DESC;