How to recursively (SQL) output parent and child?

Using Django Using the ORM
1

There is the following SQL command:

        WITH RECURSIVE menu AS (
            SELECT id, parent_id, name, CAST(name AS CHAR(500)) AS path
            FROM app_menu 
            WHERE name = %s

        UNION 

        SELECT app_menu.id, app_menu.parent_id, app_menu.name, menu.path || app_menu.name
        FROM app_menu
            JOIN menu 
                ON app_menu.parent_id = menu.id)   

        SELECT * FROM menu ORDER BY path;'''

Here I display in hierarchical order only the children of the object. How do I get my parents out too. I get an infinite loop if I write:

ON app_menu.parent_id = menu.id OR app_menu.id= menu.parent_id

I need to meet one query in SQL

2

This post is an extension of your other post on this issue. It would more appropriately be an additional response to your earlier message.

From the forum FAQ

If you have a question, please post it once and don’t make multiple posts in multiple categories to try and get attention. Sometimes it takes a while for the right person to be able to see your post and reply to it!

1 Like