mysql - Generate SQL query to draw a tree to get users with role_id 1 only

Question

Need to get array of all the users with role_id 1 only under a specified users to draw a tree, I have a MLM table which contain the user details and ancestor table which contain all the ancestors ie. parents of the users.

mlm table:

Ancestors table:

ancestor table has role_id of parent in it which is same in the mlm table too. the tree structure will be like this.

Expected output tree stucture:

I have created the tree drawing now i need the users. I placed a field called orders to know the depth.

"SELECT *
FROM   mlm
WHERE  parent >= " . $id . "
       AND role_id = '1'
       AND user_id IN (SELECT DISTINCT ans.parent_id AS USER
                       FROM   ancestors ans
                              INNER JOIN (SELECT user_id
                                          FROM   ancestors
                                          WHERE  parent_id IN (SELECT user_id
                                                               FROM   mlm
                                                               WHERE  parent =
                                                              " . $id . "
                                                                      AND
                                                              role_id != '1'))
                                         AS anse
                                      ON ans.user_id = anse.user_id
                       WHERE  ans.role_id = '1'
                              AND ans.orders = (SELECT Min(ans.orders)
                                                FROM   ancestors ans
                                  INNER JOIN (SELECT user_id
                                              FROM   ancestors
                                              WHERE
                                  parent_id IN(SELECT user_id
                                               FROM   mlm
                                               WHERE  parent =
                                              " . $id . "
                                                      AND
                                              role_id != '1'))
                                             AS anse
                                          ON
                                  ans.user_id = anse.user_id
                                                WHERE  ans.role_id = '1')
                       ORDER  BY ans.orders)
UNION
SELECT *
FROM   mlm
WHERE  parent = " . $id . "
       AND role_id = '1'";

This is the query i currently used but doesn't produce all users with role id 1. The main problem is compressing other users. I may have a user as my grand child which might break the tree.

标签： mysql