My table design is
users (id, username..., parent_id)
Data is
id username parent_id
-------------------------------
1 | admin | null
2 | reseller 1 | 1
3 | client 1 | 1
4 | reseller 2 | 1
5 | reseller 3 | 2
6 | reseller 4 | 2
7 | client 2 | 5
8 | client 3 | 6
I want to get all descendants of id
1
I studied Adjacency List, Nested List & Closure table design, but came to conclusion that Session based Adjacency List can be better in my situation.
I found an example at https://stackoverflow.com/questions/8104187/hierarchical-queries-in-mysql
SELECT @id :=
(
SELECT senderid
FROM mytable
WHERE receiverid = @id
) AS person
FROM (
SELECT @id := 5
) vars
STRAIGHT_JOIN
mytable
WHERE @id IS NOT NULL
I tried to modify this query to get my result like this
SELECT group_concat(@id :=
(
SELECT id
FROM users
WHERE parent_id = @id
)) AS u
FROM (
SELECT @id := 1
) vars
STRAIGHT_JOIN
users
WHERE @id IS NOT NULL
But it is not working. Here is the SQLFiddle
Need help in making the query work.
Best Answer
The query in the linked answer is a "hack" as the OP there clearly describes in a comment and can stop working any time with a MySQL upgrade as the exact behaviour of session variables is not documented.
Aside from that, the question there was about finding all ancestors, not all descendants. In adjacency lists, a node has maximum one parent but possibly many children. So this query/hack will not work in your case.
The Adjacency List model is the simplest of all hierarchy models in SQL. The other 3 (Nested Sets, Evaluated Path, Closure Table) are more complicated because - in a way - store redundant information. In exchange, some queries are far more easier to write. In any of the other 3 models, the solution would be really simple and wouldn't need a recursive query.
So, it seems you have two options:
Find highest level of a hierarchical field: with vs without CTEs.
You'll find more useful information in the links:
Trees and Other Hierarchies in MySQL, in Get It Done With MySQL 5&6, Chapter 20. Copyright © Peter Brawley and Arthur Fuller 2011.
EXPLAIN EXTENDED
: Hierarchical queries in MySQL, by Quassnoi (yes, it's the same one that posted the query you tried.) In his blog, he has several hierarchical queries that can find ancestors, descendants, etc.Models for hierarchical data, for MySQL, by Bill Karwin. A slideshare describing the pros and cons of various hierarchical models in MYSQL. Slightly skewed in favour or the Closure Table.