The implementation of finding all child nodes by SQL parent node

Time:2020-3-28

How to save tree data? The parent node method is commonly used in SQL. Create the table as follows


CREATE TABLE category ( id LONG, parentId LONG, name String(20) )

INSERT INTO category VALUES ( 1, NULL, 'Root' )
INSERT INTO category VALUES ( 2, 1, 'Branch1' )
INSERT INTO category VALUES ( 3, 1, 'Branch2' )
INSERT INTO category VALUES ( 4, 3, 'SubBranch1' )
INSERT INTO category VALUES ( 5, 2, 'SubBranch2' )

Where parent ID represents the parent node and name is the node name.

Suppose you want to get all the children of a node (get descendants), what should you do? If we use the program (Java / PHP) recursive call, it will access back and forth between the database and the local development language, which is very inefficient. So we hope that it can be done at the database level – what should we do?

Recursive method

After query, the best method (in my opinion) is SQL recursive CTE method. CTE means common table expression. Netizens commented: “CTE is a very elegant existence. The biggest benefit of CTE is the improvement of code readability, which is one of the necessary qualities of good code. Using recursive CTE can make it easier and more pleasant to implement complex queries in an elegant and concise way. ” ——In fact, I’m not familiar with SQL. You can google it.

How to use CTE? We use small database SQLite, it supports! Although it is small in size, it can also support the latest with statement of sql99. The example is as follows.


WITH w1( id, parentId, name) AS 
(		SELECT 
			category.id, 
			category.parentId, 
            category.name
		FROM 
			category 
		WHERE 
			id = 1
	UNION ALL 
		SELECT 
			category.id, 
			category.parentId, 
            category.name
		FROM 
			category JOIN w1 ON category.parentId= w1.id
) 

Select * from W1; where id = 1 is the ID of the parent node, you can change it to your variable. In short, a recursive CTE contains at least two queries (also known as members). The first query is a fixed-point member. A fixed-point member is just a query that returns a valid table, which is used as the basis or anchor point of recursion. The second query is called a recursive member, which is triggered by a recursive reference to the CTE name. Logically, the internal application of CTE name can be understood as the result set of the previous query. There is no explicit recursive termination condition for a recursive query. Only when the second recursive query returns an empty result set or exceeds the maximum number of recursions can the recursion stop. The upper recursion limit is achieved by using maxrecursion.

The method to find all the parent nodes is given accordingly (to get the ancestors, i.e. to reverse the ID and parentid)


WITH w1( id, parentId, name, level) AS  
(    SELECT  
        id,  
        parentId,  
        name,
        0 AS level
      FROM  
        category  
      WHERE  
        id = 6 
    UNION ALL  
      SELECT  
        category.id,  
        category.parentId,  
        category.name ,
        level + 1
      FROM  
        category JOIN w1 ON category.id= w1.parentId
 )  
SELECT * FROM w1; 

Helpless MySQL

SQLite OK, and MySQL?

On the other side of the box, mysql, which everyone loves to use, ignores the with statement. The official blog clearly states that it is not supported at all, which is very inconvenient. Why can’t it be used for simple things? ——And MySQL doesn’t seem to plan to add the CTE function of with in a new version. So we came up with a lot of ideas. Isn’t it really a recursive program? Shouldn’t it be difficult? It’s always the right line to write a function or a stored procedure? That’s right. It’s true that – it’s not a problem to write recursion, but it’s a problem to write recursion in SQL – it’s still that sentence, “every row is like a mountain”. Although it’s a bit exaggerated, I think there are not many people who understand both database and various database dialect writing methods (stored procedures) – no more details, anyway, it’s just code posts~

I won’t paste SQL here. You can see “query all the child nodes in the tree in MySQL”

At this point, our goal can be said to have been achieved, but also good, because this is unlimited number of layers (previously CMS often said “infinite level” classification). ——In fact, in general, there are many layers with more than three layers, which is very complex. Ordinary users can’t use such layers without special needs. So, under the constraint of a given number of layers, you can write standard SQL to complete the task — although it’s a bit like a dead letter~~


SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parentId = t1.id
LEFT JOIN category AS t3 ON t3.parentId = t2.id
LEFT JOIN category AS t4 ON t4.parentId = t3.id
WHERE t1.id= 1

The method to find all the parent nodes is given accordingly (to get the ancestors, i.e. to reverse the ID and parentid)

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 
FROM category AS t1 
 LEFT JOIN category AS t2 ON t2.id= t1.parentId
 LEFT JOIN category AS t3 ON t3.id= t2.parentId
 LEFT JOIN category AS t4 ON t4.id= t3.parentId

Where T1. Id = 10 optimized version

But the generated result is a little strange compared with the first example, and it’s not easy to use for Java, so look for other examples

SELECT   
            p1.id,
            p1.name,
            p1.parentId as parentId,
            p2.parentId as parent2_id,
            p3.parentId as parent3_id,
            p4.parentId as parent4_id,
            p5.parentId as parent5_id,
   p6.parentId as parent6_id
FROM category p1
LEFT JOIN   category p2 on p2.id = p1.parentId
LEFT JOIN   category p3 on p3.id = p2.parentId
LEFT JOIN   category p4 on p4.id = p3.parentId 
LEFT JOIN   category p5 on p5.id = p4.parentId 
LEFT JOIN   category p6 on p6.id = p5.parentId
WHERE 1 IN   (p1.parentId,
                   p2.parentId,
                   p3.parentId,
                   p4.parentId,
                   p5.parentId,
                   p6.parentId)

Order by 1, 2, 3, 4, 5, 6, 7; it looks like something, and it turns out to be like this.

The method to find all the parent nodes is given accordingly (to get the ancestor ancesters, i.e. reverse the ID and parentid, and change the field name in in)


SELECT   
        p1.id, 
        p1.name, 
        p1.parentId as parentId, 
        p2.parentId as parent2_id, 
        p3.parentId as parent3_id
  FROM  category p1 
  LEFT JOIN  category p2 on p2.parentId  = p1.id
  LEFT JOIN  category p3 on p3.parentId  = p2.id
  WHERE 9 IN  (p1.id,  
            p2.id,  
            p3.id)  
  ORDER BY 1, 2, 3; 

This is very common. No matter you SQLite or mysql.

Other queries:

Total number of direct child nodes queried:


SELECT c.*
,    (SELECT COUNT(*) FROM category c2 WHERE c2.parentId = c.id) 
    AS direct_children
FROM category c

Use with statement recursion, easy to understand example (English), my first successful example is copy from here, in addition, you can also check the level of layers and the reverse parent node: https://www.valentina-db.com/dokuwiki/doku.php? Id = Valentina: Articles: recursive Ou query

• origin of standard writing: http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query

Good summary post (in English): http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Http://blog.csdn.net/aflyeaglenku/article/details/50978986

• tree structure using closures (the book says that this method is the best, but also very advanced, English) http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitional-closure-extension/

The implementation method of finding all the child nodes in the above SQL parent node is all the content shared by Xiaobian. I hope it can give you a reference, and I hope you can support developepaer more.