Selecting rows from a table using tree order

Question

I have a witch table that contains the fields: id, parent_id, name (etc.)

I want to order this table in a "tree moving order", i.e.

id parent_id 1, 0 3, 1 5, 1 2, 0 8, 2 4, 0 9, 4 

(...)

briefly describe: take root node, add all children, take next node root add files, etc.

Answer 1

According to your description, I assume that you mean first-order width, which can be easily done using the WITH RECURSIVE query (PostgreSQL 8.4+):

 WITH RECURSIVE tree AS ( SELECT node_name, id, parent_id, NULL::varchar AS parent_name FROM foo WHERE parent_id IS NULL UNION SELECT node_name, f1.id, f1.parent_id, tree.node_name AS parent_name FROM tree JOIN foo f1 ON f1.parent_id = tree.id ) SELECT node_name, empno, parent_id, node_name FROM tree; 

You can also use the depth order using the following SQL:

 WITH RECURSIVE tree AS ( SELECT node_name, id, parent_id, NULL::varchar AS parent_name, id::text AS path FROM foo WHERE parent_id IS NULL UNION SELECT node_name, f1.id, f1.parent_id, tree.node_name AS parent_name, tree.path || '-' || f1.id::text AS path FROM tree JOIN foo f1 ON f1.parent_id = tree.id ) SELECT node_name, empno, parent_id, node_name, path FROM tree ORDER BY path; 

Answer 2

As noted by synergetics, the deep order solution provided by Diogo Biazus will not work for id with a different number of digits.

But you can use this solution instead, which uses integer arrays:

 WITH RECURSIVE tree AS ( SELECT node_name, id, parent_id, NULL::varchar AS parent_name, array[id] AS path FROM foo WHERE parent_id IS NULL UNION SELECT node_name, f1.id, f1.parent_id, tree.node_name AS parent_name, tree.path || f1.id AS path FROM tree JOIN foo f1 ON f1.parent_id = tree.id ) SELECT node_name, empno, parent_id, node_name, path FROM tree ORDER BY path; 

Answer 3

You can also use the excellent LTree module, but you need to reorganize your data a bit.

Answer 4

 SELECT * FROM table ORDER BY id,parent_id 

This should arrange my columns in the order set in the query.

If you do not mean GROUP elements that I think you are doing, then use

 SELECT * FROM table ORDER BY id GROUP BY parent_id 

And I also advise you to read this article: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/