0

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

This is not duplicate of How to do the Recursive SELECT query in MySQL? as this deals with one to many relation between parent and children, and the one-to=many relation is creating problem in query.

Community
  • 1
  • 1
Ehs4n
  • 762
  • 1
  • 8
  • 24
  • I'm quite confused. Why won't `SELECT * FROM users WHERE parent_id = 1;` work for you in this scenario? – CaptainAdams Jan 15 '16 at 18:29
  • @CaptainAdams Because "Reseller 2" is a descendent of "Admin", but only indirectly. The OP wants, I believe, all children, grand children, great grand children and so on. – bishop Jan 15 '16 at 18:32
  • @CaptainAdams - I need to get all descendants i.e, children, grand children and so on and not only immediate children. – Ehs4n Jan 15 '16 at 18:39
  • @RyanVincent - It doesn't work, and I need help in fixing that. If the parent has single child, it works, but if the parent has multiple children, the query shows error. – Ehs4n Jan 15 '16 at 18:41
  • You're basically writing a recursive query. A lot of databases have operators for this (CONNECT BY and START WITH in Oracle, for example, and WITH RECURSIVE in Postgres.) Not sure if MySQL has such a feature. – Mike Christensen Jan 15 '16 at 18:46
  • MySql doesn't have one. That is why I found session variables as viable solution. http://dev.mysql.com/doc/refman/5.7/en/user-variables.html – Ehs4n Jan 15 '16 at 18:47
  • How big is the table? Is it something you can just load every row into memory and work with it in the middle tier? – Mike Christensen Jan 15 '16 at 18:50
  • It can grow to few thousands only, so not a killer on memory. – Ehs4n Jan 15 '16 at 18:52
  • Yea, I don't think MySql is really built to work with hierarchical data like that, at least not write-intensive data. Not sure on the session based approach, as I don't know MySql. Even if it works, I'd question the performance even with a few thousand rows. – Mike Christensen Jan 15 '16 at 18:57
  • Possible duplicate of [How to do the Recursive SELECT query in MySQL?](http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql) – Stavr00 Jan 15 '16 at 18:58
  • @Stavr00 - Not really. My question is quite different, even though this too deals with hierarchical relations. Edited the question to explain it. – Ehs4n Jan 15 '16 at 19:10
  • GROUP_CONCAT when you are done with walking via straigtforward relational structures. – philipxy Jan 16 '16 at 01:09

1 Answers1

0

Finally, I created a function as suggested by @RolandoDBA at https://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes/7161#7161

DELIMITER $$

DROP FUNCTION IF EXISTS `siblings` $$
CREATE FUNCTION `siblings` (GivenID INT) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN

    DECLARE rv,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';
    SET queue = GivenID;
    SET queue_length = 1;

    WHILE queue_length > 0 DO
        SET front_id = FORMAT(queue,0);
        IF queue_length = 1 THEN
            SET queue = '';
        ELSE
            SET pos = LOCATE(',',queue) + 1;
            SET q = SUBSTR(queue,pos);
            SET queue = q;
        END IF;
        SET queue_length = queue_length - 1;

        SELECT IFNULL(qc,'') INTO queue_children
        FROM (SELECT GROUP_CONCAT(id) qc
        FROM `users` WHERE parent_id = front_id) A;

        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_children;
            ELSE
                SET rv = CONCAT(rv,',',queue_children);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;

    RETURN rv;

END $$

And then for getting siblings / children of a parent, a simple call using the above created function, siblings is enough.

SELECT siblings(id) AS `siblings` from `users` where `id` = 1

This returns result as 

siblings
---------------
2,3,4,5,6,7,8
Community
  • 1
  • 1
Ehs4n
  • 762
  • 1
  • 8
  • 24