# 계층 쿼리

{{tag>postgresql Hierarchical Query}}

```
WITH recursive category_hq(id, name, parent_id, children, brand_id, depth, path, cycle) as (
	SELECT id, name, parent_id, children, brand_id,
	1, array[id], false
	FROM brand_category
	WHERE id = 16
	
	UNION ALL
	
	SELECT c.id, c.name, c.parent_id, c.children, c.brand_id,
	p.depth + 1, array_append(p.path, c.id), c.id = any(p.path)
	
	FROM brand_category c, category_hq p
	WHERE p.id = c.parent_id
	AND not cycle	
)

SELECT * from category_hq
order by path
```


```
article_reply_qs = ArticleReply.objects.raw(f"""            
WITh RECURSIVE reply_parent(id, user_id, parent_id, reply, depth, path, cycle) as (            
    SELECT 	id, 
            user_id,
            parent_id, 
            reply, 
            1, 
            array[id],
            false			
    FROM rank_articlereply
    WHERE parent_id is null
        AND article_id = {article_id}
                
    UNION ALL
                
    SELECT 	c.id, 
            c.user_id,
            c.parent_id,
            c.reply, 
            p.depth+1,
            array_append(p.path, c.id),
            c.id = any(p.path)
    FROM rank_articlereply c, reply_parent p
    WHERE p.id = c.parent_id
            AND not cycle                        
)
            
SELECT * from reply_parent
order by path
""")
```