How to query the MySql table to display the root and its alkalis.

Question

UserID UserName ParentID TopID 1 abc Null Null 2 edf 1 1 3 gef 1 1 4 huj 3 1 5 jdi 4 1 6 das 2 1 7 new Null Null 8 gka 7 7 

TopID and ParentID is the user ID

I want to get a user record and a record of its child and child record. Here, userid1 is the root and its child is userid2 and userid 3. So, if the user ID is 1, I have to map all entries from userid 1 to userid 6, since they are all children and SUbchild of the root. Similarly for userid3 I have to display userid3 and its child element Userid 4 and Child of Userid 4 Userid5 if the user ID is 3

the conclusion should be

 Userid Username 3 gef 4 huj 5 jdi 

I will know the user ID and topID so that I can execute the request to achieve the above result.

 SELECT UserID, UserName FROM tbl_User WHERE ParentID=3 OR UserID=3 And TopID=1; 

In the above request, I can display userid 3 and userid 4 I can not display userid 5, the view hit it. Need help. Thanks

Answer 1

It is technically possible to perform recursive hierarchical queries in MySQL using stored procedures.

Here is one adapted to your scenario:

 CREATE TABLE `user` ( `UserID` int(16) unsigned NOT NULL, `UserName` varchar(32), `ParentID` int(16) DEFAULT NULL, `TopID` int(16) DEFAULT NULL, PRIMARY KEY (`UserID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO user VALUES (1, 'abc', NULL, NULL), (2, 'edf', 1, 1), (3, 'gef', 1, 1), (4, 'huj', 3, 1), (5, 'jdi', 4, 1), (6, 'das', 2, 1), (7, 'new', NULL, NULL), (8, 'gka', 7, 7); DELIMITER $$ DROP PROCEDURE IF EXISTS `Hierarchy` $$ CREATE PROCEDURE `Hierarchy` (IN GivenID INT, IN initial INT) BEGIN DECLARE done INT DEFAULT 0; DECLARE next_id INT; -- CURSOR TO LOOP THROUGH RESULTS -- DECLARE cur1 CURSOR FOR SELECT UserID FROM user WHERE ParentID = GivenID; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- CREATE A TEMPORARY TABLE TO HOLD RESULTS -- IF initial=1 THEN -- MAKE SURE TABLE DOESN'T CONTAIN OUTDATED INFO IF IT EXISTS (USUALLY ON ERROR) -- DROP TABLE IF EXISTS OUT_TEMP; CREATE TEMPORARY TABLE OUT_TEMP (userID int, UserName varchar(32)); END IF; -- ADD OURSELF TO THE TEMPORARY TABLE -- INSERT INTO OUT_TEMP SELECT UserID, UserName FROM user WHERE UserID = GivenID; -- AND LOOP THROUGH THE CURSOR -- OPEN cur1; read_loop: LOOP FETCH cur1 INTO next_id; -- NO ROWS FOUND, LEAVE LOOP -- IF done THEN LEAVE read_loop; END IF; -- NEXT ROUND -- CALL Hierarchy(next_id, 0); END LOOP; CLOSE cur1; -- THIS IS THE INITIAL CALL, LET GET THE RESULTS -- IF initial=1 THEN SELECT * FROM OUT_TEMP; -- CLEAN UP AFTER OURSELVES -- DROP TABLE OUT_TEMP; END IF; END $$ DELIMITER ; CALL Hierarchy(3,1); +--------+----------+ | userID | UserName | +--------+----------+ | 3 | gef | | 4 | huj | | 5 | jdi | +--------+----------+ 3 rows in set (0.07 sec) Query OK, 0 rows affected (0.07 sec) CALL Hierarchy(1,1); +--------+----------+ | userID | UserName | +--------+----------+ | 1 | abc | | 2 | edf | | 6 | das | | 3 | gef | | 4 | huj | | 5 | jdi | +--------+----------+ 6 rows in set (0.10 sec) Query OK, 0 rows affected (0.10 sec) 

Time to indicate some reservations :

• Since this calls the stored procedure recursively, you need to increase the size of max_sp_recursion_depth , which has a maximum value of 255 (default is 0).

• My results on an unoccupied server with limited test data (10 tuples in the user table) took 0.07-0.10 seconds. The performance is such that it is best to place recursion at your application level.

• I have not used your TopID column, so there may be a logical flaw. But two tests gave me the expected results.

_{Disclaimer: This example was just to show that this can be done in MySQL, but not that I approve of it. Stored procedures, temporary tables, and cursors may not be the best way to solve this problem.}

Answer 2

Well, not a pretty implementation, but since you only need children and children, any of them can work:

Query1:

 SELECT UserID, UserName FROM tbl_user WHERE ParentID = 3 OR UserID = 3 UNION SELECT UserID, UserName FROM tbl_user WHERE ParentID IN (SELECT UserID FROM tbl_user WHERE ParentID = 3); 

Request 2:

 SELECT UserID, UserName FROM tbl_user WHERE UserID = 3 OR ParentID = 3 OR ParentID IN (SELECT UserID FROM tbl_user WHERE ParentID = 3); 

EDIT 1: Alternatively, you can change the structure of your spreadsheet to make it more convenient to query all children in a particular category. Please follow this link to learn more about storing hierarchical data in MySQL .

In addition, you can think about saving your data in a hierarchical tree-like manner, which is very well explained in this article .

Please note that each method has its own trade-offs regarding obtaining the desired results compared to adding / removing categories, but I'm sure you will enjoy reading.

Answer 3

This is one of the best articles I've seen to explain the Modified Tree Preview Order method for storing tree-like data in an SQL-style database.

http://www.sitepoint.com/hierarchical-data-database/

MPTT material starts on page 2.

Essentially, you store the “Left” and “Right” values ​​for each node in the tree, so that to get all the ParentA children, you get Left and Right for ParentA, then

 SELECT * FROM TableName WHERE Left > ParentLeft AND Right < ParentRight 

Answer 4

To get all the parents of the selected child (user_id = 3 in this example):

 SELECT @parent_id AS _user_id, user_name, ( SELECT @parent_id := parent_id FROM users WHERE user_id = _user_id ) AS parent FROM ( -- initialize variables SELECT @parent_id := 3 ) vars, users u WHERE @parent_id <> 0; 

To get all the children of the selected user_id

 SELECT ui.user_id AS 'user_id', ui.user_name AS 'user_name', parent_id, FROM ( SELECT connect_by_parent(user_id) AS user_id FROM ( SELECT @start_user := 3, @user_id := @start_user ) vars, users WHERE @user_id IS NOT NULL ) uo JOIN users ui ON ui.user_id = uo.user_id 

This requires the following function

 CREATE FUNCTION connect_by_parent(value INT) RETURNS INT NOT DETERMINISTIC READS SQL DATA BEGIN DECLARE _user_id INT; DECLARE _parent_id INT; DECLARE _next INT; DECLARE CONTINUE HANDLER FOR NOT FOUND SET @user_id = NULL; 

 SET _parent_id = @user_id; SET _user_id = -1; IF @user_id IS NULL THEN RETURN NULL; END IF; LOOP SELECT MIN(user_id) INTO @user_id FROM users WHERE parent_id = _parent_id AND user_id > _user_id; IF @user_id IS NOT NULL OR _parent_id = @start_with THEN RETURN @user_id; END IF; SELECT user_id, parent_id INTO _user_id, _parent_id FROM users WHERE user_id = _parent_id; END LOOP; 

END

code>

This example uses session variables heavily, which many sql users may not be familiar with, so there is a link here that might give some idea: session variables