Tree sorting

Question

There is such a table, which is a tree table of contents:

CREATE TABLE "tree" ( "Id" INTEGER NOT NULL, "Name" TEXT NOT NULL, "Parent" INTEGER, "SortKey" INTEGER NOT NULL, CONSTRAINT "Id" UNIQUE ("Id") ); INSERT INTO "tree" VALUES (1, 'Текст1' , null, 1); INSERT INTO "tree" VALUES (2, 'Текст2' , null, 0); INSERT INTO "tree" VALUES (3, 'Текст3' , null, 1); INSERT INTO "tree" VALUES (4, 'Текст1.1', 1 , 2); INSERT INTO "tree" VALUES (5, 'Текст1.2', 1 , 1); INSERT INTO "tree" VALUES (6, 'Текст3.1', 3 , 0);

Where the Parent field is a link to the "parent" entry. If it is NULL , then the entry is in the root. Tell me, please, what kind of query you can display this table of contents in a sorted form.

Option 1

Ordering in adjacent records ("descendants" of the same "parent") follows the Name field:

 1, 'Текст1' , null, 1 4, 'Текст1.1', 1 , 2 5, 'Текст1.2', 1 , 1 2, 'Текст2' , null, 0 3, 'Текст3' , null, 1 6, 'Текст3.1', 3 , 0

Option 2

Sorting in adjacent records ("descendants" of one "parent") follows the SortKey field:

 2, 'Текст2' , null, 0 1, 'Текст1' , null, 1 5, 'Текст1.2', 1 , 1 4, 'Текст1.1', 1 , 2 3, 'Текст3' , null, 1 6, 'Текст3.1', 3 , 0

I understand that the requests should in fact be identical - the only difference is in the sort field. Led so for completeness.

Interesting question) It is desirable, without limitation, within reasonable limits.

Accepted Answer · 2017-01-28T11:24:10

We need to collect the "path" of the tree to each sheet and sort by it. Since the path is a text field, the numeric sort key will need to be padded with zeros on the left to sort correctly. For a text key sorting this is not required. Since your sort key is not unique, you had to add sorting by id within the same sortKey, otherwise the lines are not aligned correctly.

To sort by SortKey, the request will look like this:

 with recursive Q(id, name, parent,path) as( select id, name, parent, substr('0000000000' || SortKey, -10, 10)||substr('0000000000' || id, -10, 10) from tree where Parent is null union all select T.id, T.name, T.parent, path||'/'||substr('0000000000' || T.SortKey, -10, 10)||substr('0000000000' || T.id, -10, 10) from Q, tree T where T.parent=Q.id ) select * from Q order by path

To sort by name, the entire assembly from substr() replaced by the name field. If you have the same name at the same sort level, you will need to add the record id as in the example above.

Test on sqlfiddle.com

As I understand it, if the depth is great - you get great ways?
it makes sense for numeric fields to make additions shorter, if there are no such large numbers
And why not just use depth tree traversal, then Q immediately returns the records in the correct order?
I did not know that some DBMS support order by in the recursive part

Tree sorting

1 answer 1

More articles: