Get full tree of parent/child relationships in mysql of any node in the tree with all parents

Question

Example data:

+----+-------+----------+
| org_id | Name  | ParentID |
+----+-------+----------+
|  1 | Org1  | 2        |
|  2 | Org2  | NULL     |
|  3 | Org3  | 5        |
|  5 | Org5  | 1        |
| 14 | Org14 | 1        |
+----+-------+----------+

If I am logged in as user with org_id 1 (Org1). I want to retrieve the full tree of that user.

I have the following recursive Query :

WITH RECURSIVE cte (org_id, name, parent_id) AS (
     SELECT org_id, name, parent_id
     FROM organization
     WHERE org_id = 1
     UNION ALL
     SELECT t1.org_id, t1.name, t1.parent_id
     FROM organization t1
     INNER JOIN cte t2 ON t1.parent_id = t2.org_id
)
SELECT * FROM cte;

However, this query only gives me the children of the current id (Org1 in this example). How can I include all the parents as well in the result set, so I can rebuild the entire tree accurately?

EDIT: I am using MariaDB version 10.4.10

EDIT: I tried the query as in the answer below, i'm getting a syntax error :

Is it MySQL or MariaDB? Those are two very different animals as of 2020. (also what version?) — The Impaler, Feb 01 '20 at 16:24
Your query works fine. The problem must be elsewhere. See https://www.db-fiddle.com/f/7QeMCpSyrrxQZwBLgn3KbS/0 — The Impaler, Feb 01 '20 at 16:30

slaakso · Accepted Answer · 2020-02-03T10:00:04.213

1

You have a CTE that gets the children. Why not use another to go to opposite direction and get the parents:

MySQL:

(WITH RECURSIVE cte (id, name, parent_id) AS (
     SELECT id, name, parent_id
     FROM organization
     WHERE id = 1
     UNION  
     SELECT t1.id, t1.name, t1.parent_id
     FROM organization t1
       INNER JOIN cte t2 ON t1.parent_id = t2.id 
)
SELECT * FROM cte)
UNION
(WITH RECURSIVE cte (id, name, parent_id) AS (
     SELECT id, name, parent_id
     FROM organization
     WHERE id = 1
     UNION 
     SELECT t1.id, t1.name, t1.parent_id
     FROM organization t1
       INNER JOIN cte t2 ON t2.parent_id = t1.id 
)
SELECT * FROM cte)

And a version which works both in MySQL and MariaDB:

MySQL/MariaDB:

WITH RECURSIVE cte (id, name, parent_id, dir) AS (
     SELECT id, name, parent_id, cast(null as char(10)) as dir
     FROM organization
     WHERE id = 1
     UNION  
     SELECT t1.id, t1.name, t1.parent_id, ifnull(t2.dir, 'down')
     FROM organization t1
       INNER JOIN cte t2 ON t1.parent_id = t2.id and ifnull(t2.dir, 'down')='down'
     UNION
     SELECT t1.id, t1.name, t1.parent_id, ifnull(t2.dir, 'up')
     FROM organization t1
       INNER JOIN cte t2 ON t2.parent_id = t1.id and ifnull(t2.dir, 'up')='up'
)
SELECT id, name, parent_id FROM cte;

See db-fiddle and dbfiddle

edited Feb 03 '20 at 10:00

answered Feb 01 '20 at 17:08

slaakso

8,331
2
16
27

Thanks for your reply! It seems to be the correct way to go about this, but i cannot get it working. I keep getting syntax errors "(near "WITH" at position 1)". I can execute the first part of the query (before the UNION) only without the parentheses around it, but if they are added, it doesnt work anymore – Dennis Feb 03 '20 at 08:38
I have edited my original post, i keep getting syntax errors. Is it a different syntax for MariaDB ? – Dennis Feb 03 '20 at 08:51
I have created a DB fiddle with mariaDB: https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=6aea0acb5f969e8c81ccc79ca9922f95 – Dennis Feb 03 '20 at 09:01
Looks like MariaDB does not like the parentheses around CTE the same way MySQL does. Added MariaDB compartible version. – slaakso Feb 03 '20 at 09:13
I've tried the mariadb edit but it doesn't return the correct results. Also looking at the dbfiddle, it is missing Org3 in that db fiddle. – Dennis Feb 03 '20 at 09:22
I managed to hack around it using your suggestion to turn around the org_id and parent_id. So i'm traversing from my current element up untill i found the "ultimate" parent of the tree. When i've got that one, i'll do the original query to get the entire tree below that one. I'll mark the answer as accepted since it helped me a great deal of understanding how to tackle this issue. Thanks a lot again. – Dennis Feb 03 '20 at 09:33
1

Turned out that the NULL needs to be casted to matching datatype. Could be considered a bug though. Works now both in MariaDB and MySQL. – slaakso Feb 03 '20 at 10:02
This is great! Thank you. I never realized there were so big differences between mysql and mariadb regarding this issue. – Dennis Feb 03 '20 at 10:18
Compatibility to MySQL 5.7 is very good. New features like this might have differencies but this one could/should be easily fixed. – slaakso Feb 03 '20 at 10:22

Get full tree of parent/child relationships in mysql of any node in the tree with all parents

1 Answers1