Sometimes data in a DB table needs to point to parent or child data. The result is a hierarchy. Here's an example of how folders on a file system might be represented:
In the example, Top and Other Top are in the root (/ or C:\, depending whether you're from Unix world or Windows land).
So, if I want to get all of the children and grandchildren of "Top" I can use a common table expression. This acts like a recursive query (technically it's iterative, not recursive - see the "how it works" part later for details).
A few parts are involved (jump to see the whole thing first if you prefer then come back here for the parts. Go on, I'll wait...):
- A definition for the recursive query
WITH RECURSIVE foldertree(id, parentid, name) AS
- A starting point. In our case we want to begin at id=1;
SELECT id, parent_id, name FROM folder WHERE id = 1
- A UNION to add rows from the following query...
- A query that finds the child rows we want, based on what we found last time. In the first iteration, what we found last time will be the row(s) from the starting point. In second iteration, what we found last time will be the things from the first iteration, etc.
SELECT folder.id, folder.parentid, folder.name FROM folder, foldertree WHERE folder.parentid = foldertree.idThe results from each iteration are also set aside to be returned as results via the final part as follows...
- Finally, something that uses the WITH query to return results. In this case, all of them will do:
SELECT * FROM folder_tree
The whole query looks like this (back to the parts):
WITH RECURSIVE folder_tree(id, parent_id, name) AS ( SELECT id, parent_id, name FROM folder WHERE id = 1 UNION SELECT folder.id, folder.parent_id, folder.name FROM folder, folder_tree WHERE folder.parent_id = folder_tree.id ) SELECT * FROM folder_tree
The results include everything except the "Other Top" row:
We can also build a path as we go, to show how we got to a given folder:
WITH RECURSIVE folder_tree(id, parent_id, name) AS ( SELECT id, parent_id, name, name AS path FROM folder WHERE id = 1 UNION SELECT folder.id, folder.parent_id, folder.name, folder_tree.path || '/' || folder.name as path FROM folder, folder_tree WHERE folder.parent_id = folder_tree.id ) SELECT * FROM folder_tree
|3||1||Child 2||Top/Child 2|
|2||1||Child 1||Top/Child 1|
|4||2||Grand child||Top/Child 1/Grand child|
How it works