I'm looking to build a question answer survey system where some questions will be based on the answer of the parent question. The hierarchy level of the questions can go any number of depth based on the questions. The questions and answers will be like the diagram shown here. I'm looking to build this in mongodb. I have checked the tree model in mongodb and I'm clueless on how to structure it as the next set of questions are depends on the answers. The questions should show based on what answer the user have selected and some answers may have multiple question sets below that as well (E.g.: Ans B). Can someone help me on the mongodb collection structure and how can I query the next set of questions and answers based on the previous answer given by the user? Thanks in advance.

I am using MySQL in trying to solve a problem relating to a SQL query classifying types of nodes in a binary tree . Specifically, the question states as follows: > You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N. Both columns have datatype as integer. Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node: > > - Root: If node is root node. > - Leaf: If node is leaf node. > - Inner: If node is neither root nor leaf node. Here is my attempt so far:

SELECT N,  
CASE  
    WHEN bst_outer.P IS NULL THEN "Root"  
    WHEN NOT EXISTS(SELECT bst_inner.N FROM BST bst_inner WHERE bst_inner.P = bst_outer.P) THEN "Leaf"  
    ELSE 'Inner'  
END   
FROM BST bst_outer;

The output after running the above query gives incorrect result, as there appears to be no nodes with Leaf classification. It seems like that the error lies on my second WHEN statement, but I am not sure how to fix it.

As far I can see, r-tree indexes are used today mostly for geographic systems, for spatial searches. I need some very similar, but not in space, but in 1-D time series. Imagine a *huge* time series, for example floating point data of a billions of point. *I need to answer quickly questions like* > *"what was the maximal value in the interval (1345223, 23234235)"?* Note, the DB is huge, a simple SELECT MAX(value) WHERE id BETWEEN 1345223 AND 23234235 is not acceptable - at least not if it needs a linear scanning of the interval. It must happen in same type of tricky index, most likely r-tree. Maybe it helps (but more likely not): as it is a time series table, it is inherently single-column. Other index types or other database engines are okay as well. I am investigating postgresql only because it is the smartest open source engine I know.

Purely relational databases ([SQL](https://en.wikipedia.org/wiki/SQL)) seem to suffer to nowadays from the lack of a good solution for search/indexing hierarchies. Here a systematic review of the subject: https://stackoverflow.com/q/4048151 What is surprising is that no "Lineage Column" solution makes use of a **[bit string](https://en.wikipedia.org/wiki/Bit_array)** column, in particular adopting this column as the primary key. Maybe [this article, which called the strategy "Sort Path"](https://www.sqlservercentral.com/articles/hierarchies-on-steroids-1-convert-an-adjacency-list-to-nested-sets) , suggests something along those lines... But I didn't see anything more explicit. ------- ## Illustrating Fruits can form a stable (static) taxonomy for the owner of a grocery store. Below, each fruit in the grocery store's inventory received a bitString identifier. Apples were labeled with the prefix 0, oranges with the prefix 1. Within the set of apples, green apples receive the prefix 01. When using [SQL bitstring](https://www.postgresql.org/docs/current/functions-bitstring.html) the "ORDER BY" use the [**lexicographic order**](https://en.wikipedia.org/wiki/Lexicographic_order) . For example ordering all *bitstrings*, from 1 to 4 bits:

00       apple "0"
 000      apple "00"
 0000     apple "000"
 01       green apple ""
 010      green apple "0"
 011      green apple "1"
 10       orange "0"
 101      orange "01"

As we can see, the lexicographical order is grouping [taxons](https://en.wikipedia.org/wiki/Taxon) through intervals ("all apples" set, green apples subset, oranges set). BitString labels are not numbers, but can be counters using [successor function](https://stackoverflow.com/q/76859093/287948) , so is valid as Primary Keys. For example the next green apple will be 0011 and the next orange 110. In a finite tree, suppose 5 bits, we can define the function MaxCount5bits(x), to check the last child. For example for oranges MaxCount5bits('1') is 11111, for green apples MaxCount5bits('01') is 01111. That is, using [standard bitString operators](https://www.postgresql.org/docs/current/functions-bitstring.html) , MaxCount5bits(x) = substring(x || '11111',1,5). The fruit table can be something like CREATE TABLE fruit (id bitString PRIMARY KEY, info text), so we can do any tree-search operation: * find all oranges: SELECT * FROM fruit WHERE id BETWEEN '1' AND MaxCount5bits('1') * find all green apples: SELECT * FROM fruit WHERE id BETWEEN '01' AND MaxCount5bits('01') * find all apples: SELECT * FROM fruit WHERE id BETWEEN '0' AND MaxCount5bits('0') * find the parent of the apple 01: no search is necessary, it is the prefix 0, that is something as substring(x,1,length(x)-1). * find the parent of the orange 101: 10 or 1 (because it is a two-bit-prefix taxonomy). * ... reduced the taxonomic analysis to a simple "check BETWEEN at b-tree" operation, and "check ID prefix" operation. And, since it is indexed as a primary key, it is fast (the search engine uses a b-tree). ## Question In view of the illustrated, the question is: **is it the faster solution?**
That is, best performance in any SQL-database that supports bitstrings and its lexicographical order b-tree indexation. Seems also the the *most economical solution*: all operations are ID-based, no extra column, and with all integrity (reference and primary key) guaranteed. ----- PS: perhaps a more useful variant of the model is to adopt the first bits as *taxonomic control* and the remaining bits as a serial-integer counter. In the illustration, there are 2 taxonomic bits.

I have two tables node and node_tree: CREATE TABLE node ( id integer PRIMARY KEY ); CREATE TABLE node_tree ( node_id integer references node(id) UNIQUE, parent_id integer references node(id) ); node_tree is an adjacency list. There is a limit on the height of the tree, so, (1) the depth of the parent node before inserting a new node must be checked and (2), the sum of the height of the node being relocated and the depth of its new parent must not exceed the established threshold and to prevent infinite loops, it is essential to verify that the new parent is not included among the descendants of the node being relocated. Using recursive CTEs, it is easy to apply any of those constraints. But with multiple, concurrent transactions, anomalies can happen. I can think of the following solutions: 1. Locking the whole node_tree table. Although I have read locking tables is an anti-pattern and almost always the wrong thing to do. 2. Using advisory locks at the application level. 3. Using serializable isolation level. 4. Row level locking using for update. I like the last one but the problem with it is that, you cannot use for update with recursive CTEs (trust me, I have tested), and it seems complex to me. Now my question is what is the best option and why?

I have written a PostgreSQL query that's working perfectly: ~~~pgsql WITH RECURSIVE x AS ( SELECT i, parent, id, name, type, '' AS path FROM entry WHERE name = 'JS-VBNET-2' UNION ALL SELECT e.i, e.parent, e.id, e.name, e.type, (x.path || '/' || e.name) AS path FROM entry e, x WHERE x.id = e.parent ) SELECT x.i as reti, x.id as retid, x.name as retname, types.mime as retmime, x.path as retpath FROM x JOIN types ON types.i = x.type WHERE x.path = '/Index.htm'; ~~~ I tried to convert this query to a function: ~~~pgsql -- DROP FUNCTION path1(character varying,character varying); CREATE OR REPLACE FUNCTION path1(enter character varying, request character varying) RETURNS TABLE ( reti INTEGER, retid character varying, retname character varying, retmime character varying, retpath character varying ) AS $$ BEGIN WITH RECURSIVE x AS ( SELECT i, parent, id, name, type, '' AS path FROM entry WHERE name = enter UNION ALL SELECT e.i, e.parent, e.id, e.name, e.type, (x.path || '/' || e.name) AS path FROM entry e, x WHERE x.id = e.parent ) SELECT x.i as reti, x.id as retid, x.name as retname, types.mime as retmime, x.path as retpath FROM x JOIN types ON types.i = x.type WHERE x.path = request; END; $$ LANGUAGE plpgsql; GRANT EXECUTE ON FUNCTION path1(enter character varying, request character varying) TO public; ~~~ Call: select * from public.PATH1 ('JS-VBNET-2', '/Index.htm'); But I get an error: > ~~~none > ERROR: 42601: query has no destination for result data > HINT: If you want to discard the results of a SELECT, use PERFORM instead. > CONTEXT: PL/pgSQL function path1(character varying,character varying) line 3 at SQL statement > ~~~ AI hint is: > Add a destination for the result data, such as using INTO or returning the results in a SELECT statement. What is going wrong? How I can convert my function correctly?

I was able to put together a rough idea for a PostgreSQL query to query over a node table, which contains id, parent__id, slug, and file_url (optional). It's considered a file if it has file_url, otherwise it's a directory. -- Enable the pg_trgm extension for fuzzy matching CREATE EXTENSION IF NOT EXISTS pg_trgm; WITH RECURSIVE path_cte AS ( -- Base case: Start with root nodes SELECT id, slug, parent__id, slug AS path FROM node WHERE parent__id IS NULL -- Start from root nodes UNION ALL -- Recursive case: find children using similarity matching with parent path SELECT n.id, n.slug, n.parent__id, CONCAT(pc.path, '/', n.slug) AS path FROM node n JOIN path_cte pc ON n.parent__id = pc.id AND similarity(n.slug, pc.slug) > 0.3 -- Adjust the threshold as needed ) -- Select the final paths for each node SELECT id, path FROM path_cte ORDER BY path; This is where the AI and I got. However, we duly note: > Keep in mind that the similarity condition might introduce cases where nodes do not match their parent due to the similarity threshold. If no similar match is found at a given level, recursion will stop for that branch. In VSCode, I can search like this: Or even: How can I replicate that in PostgreSQL? Given my hierarchical nodes table: CREATE TABLE nodes ( id SERIAL PRIMARY KEY, -- Unique identifier for each node slug VARCHAR(255) NOT NULL, -- Name or slug of the node parent__id INT REFERENCES nodes(id) ON DELETE CASCADE, -- Parent node ID, referencing the same table file_url VARCHAR(255) -- Optional URL for file associated with the node ); Or if that is not a good table structure, then what is a good one to be able to search like this in PostgreSQL? Ideally a user can pass in a path like a Unix file path, and perhaps it splits it at the / slashes (or not), and then does a fuzzy search on each segment relative to the parent, recursively, without finding things that don't match, against a PostgreSQL schema. Can it be done? If so, how? If not, where does it become impossible, and what is possible close to this? Otherwise, this is what I'm doing in JS: export async function search({ searchTerm }: { searchTerm: string }) { return await db .selectFrom('image') .select('path') .where( sql`similarity(path, ${sql.lit( searchTerm, )}::text) > ${sql.lit(similarityThreshold)}::float`, ) .orderBy( sqlsimilarity(path, ${sql.lit(searchTerm)}::text), 'desc', ) .execute() } Doesn't seem like that will cut it.

I am trying to model hierarchical data using a RDBMS but can't quite find the right solution. We are capturing information about storage levels in a warehouse system. There are 8 possible storage levels, each with a parent-child relationship to levels above and below. Stock is then associated with the final level (Level 8). (BTW each level has a name but I'm keeping it generic for simplicity.)

Warehouse 1
  Level 1
    Level 2
      Level 3
        Levels 4-7...
          Level 8 -> associated with stock

You will always have Level 8, but you may not always have every parent Level. Here is the added complexity that is confusing me: 1. Levels can be skipped, making the hierarchy ragged as opposed to balanced. For example, a Level 6 might have Level 4 as a parent and skip Level 5. 2. The parent to a Level MUST be a level above in the hierarchy, i.e., Level 5 cannot have Level 6 as a parent; it can only be Levels 4 or less. 3. Child Levels inherit the properties of their parent. This means that if we designate a Level for "Receiving", this same property will be inherited by all child Levels. 4. Levels can be disabled by a parent. This means in a structure where you have Level 1 -> Level 2 -> Level 8, the user may later disable Level 2, making the hierarchy now Level 1 -> Level 8 and we will need to readjust the parent of all Level 8 entries in the lineage. 5. As a reminder, we always end up at Level 8. I have researched a lot but am still struggling with some things. Here's the open items I'm not sure about. 1. One route I can think of is to put all Levels in the same table, and prepare a seemingly standard adjacency list model where each Level points to its parent. I'm not sure if this is the best approach due to the added constraints based on Level Type; i.e., Levels cannot choose lower Levels as a parent, and also some parents may have disabled certain child Levels. We also need to manage inherited properties at each Level based on the properties of its parent. 2. I'm leaning towards placing each Level in a separate table, and then transforming the ragged hierarchy into a balanced hierarchy by using dummy Levels (unseen to the user) when they are skipped. In this way, Level 3 is always the parent to Level 4, for example, even if the user selects Level 2 as the parent of Level 4. This was a solution I read about in this SE post . If you have better ideas or insights to share, please do. I'm new at this and eager to learn so I apologize if some of my questions appear to be basic.

Given a table like so: | path (ltree) | |-------| | a.b.c | | a.b | | a | | d.e | | f | How would I write a query to return the longest matching ltree path given an input? For example: (input) => expected output

(a.b.c) => a.b.c
(d.e.f) => d.e
(f.g.h) => f
(a.b)   => a.b

I'd like to be able to use this to join one table containing ltree paths against their "longest matching path" in another table in a way that is performant. So given a table containing rows of all the inputs in the example above, how would I join it to the table to get rows with the "longest match"?

Having this simple many-to-many self-referential structure. An item owns other items through the joins table:

CREATE TABLE items (
  item_id   serial PRIMARY KEY
, title     text
);

CREATE TABLE joins (
  id        serial PRIMARY KEY
, item_id   int
, child_id  int
);

INSERT INTO items (item_id, title) VALUES
  (1, 'PARENT')
, (2, 'LEVEL 2')
, (3, 'LEVEL 3.1')
, (4, 'LEVEL 4.1')
, (5, 'LEVEL 4.2')
, (6, 'LEVEL 3.2')
;

INSERT INTO joins (item_id, child_id) VALUES
  (1, 2)
, (2, 3)
, (3, 4)
, (3, 5)
, (2, 6)
;

*dbfiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=a7a2ddc8d5c8f4053eed201ef359753e)* I am trying to retrieve a whole tree structure as JSON for a given item. For example, to query the item with item_id 1 (pseudo-code): SELECT i.*, fulltree from items i where item_id = 1; Desired output for fulltree: { id: 1, title: "PARENT", children: [ { id: 2, title: "LEVEL 2", children: [ { id: 3, title: "LEVEL 3.1", children: [ { id: 4, title: "LEVEL 4.1" }, { id: 5, title: "LEVEL 4.2" } ] }, { id: 6, title: "LEVEL 3.2" } ] } ] } After digging into the JSON capabilities Postgres offers, I managed such output by repeating a nested query. Simple but ugly, and limited to the amount of repeats. :/ I've found out about recursive queries. The examples found here and there are not that simple. It's hard to finding an entrypoint to understanding the technique and adapt it to my needs. I hope the example here will be simple enough to find help from experienced users.

How do I swap two record's paths/positions in an [ltree](https://www.postgresql.org/docs/current/ltree.html) using postgres, where all children follow? There is a uniqueness constraint on the path. Given the following records on a pages table:

id: 1,  path: "1",
id: 2,  path: "1.1",
id: 3,  path: "2",
id: 4,  path: "2.1",
id: 5,  path: "2.2",
id: 6,  path: "2.2.1",
id: 7,  path: "2.2.1.1",
id: 8,  path: "2.3",
id: 9,  path: "2.3.1",
id: 10, path: "2.3.1.1",
id: 11, path: "2.3.1.2",
id: 12, path: "2.3.1.3",
id: 13, path: "2.3.1.4",
id: 14, path: "2.4",
id: 15, path: "3",
id: 16, path: "3.1",
id: 17, path: "3.2",
id: 18, path: "3.2.1",
id: 19, path: "3.2.2",
id: 20, path: "3.2.3",

I can *move* record 15 (and children 16-20) to anywhere in the tree using the following:

--  $1 is newpath, $2 is oldpath
UPDATE "pages" SET "path" = $1  WHERE "pages"."id" = 15;
UPDATE "pages" SET path = $1 || subpath(path, nlevel($2)) WHERE (pages.path <@ $2) AND (pages.path != $1)

But what is the best way to to swap the positions of two records (and children)? For example: - changing the paths of ids 5 (5 keeps children 6-7) and 8 (8 keeps children 9-13) - changing the paths of ids 3 (3 keeps children 4-14) and 15 (15 keeps children 16-20) I saw [this post](https://dba.stackexchange.com/questions/262071/how-to-swap-primary-key-between-records-in-postgres) , where the author swaps a value of two rows with a temporary value (in their case they negate). Should I do a similar thing, but using the query from before three times? Is there a better way which doesn't require 6 UPDATE calls?

Currently designing a database to register animal species (Sharks and relatives) 1 table will hold species (name, attributes, and relative information) I will have the taxonomy hierarchy for each specie, then I'm thinking in create a table to hold that, eg: CREATE TABLE IF NOT EXISTS public.taxonomy ( taxonomy_id integer NOT NULL GENERATED ALWAYS AS IDENTITY, taxonomy_level_id smallint NOT NULL, path ltree NOT NULL, english_name varchar(50) COLLATE pg_catalog."default", description text COLLATE pg_catalog."default", CONSTRAINT taxonomy_pkey PRIMARY KEY (taxonomy_id), ) My question is due the ltree and best use of it. In every sample I see people registering all nodes even they are not used, for example if they have A.B.C, they will insert as: A A.B A.B.C Is all that necessary or can we just define the ending required path? (1 row just with A.B.C and then latter we can also have A.B.A) Let'me show my usage... The "full" path for a great white shark would be "**Eukariota.Animalia.Metazoa.Chordata.Vertebrata.Gnathostomata.Fish.Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Carcharodon**" As shark and relatives start at "Chondrichthyes" I plan to drop all previous nodes which are static and fixed. So the root node would be **Chondrichthyes** and I can trim the path to: "**Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Carcharodon**" Registering a row per every node is kinda painful when all I need is the finishing node for the specie. So, my question: Can I register only the required path or all nodes must register independently, even if they never be the target_id of any table? eg: Chondrichthyes Chondrichthyes.Elasmobranchii Chondrichthyes.Elasmobranchii.Selachimorpha Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Carcharodon Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Lamna Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Isurus VS Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Carcharodon Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Lamna Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Isurus

My table in the database How can I get my result as Where child = 'chl1'?

I am trying to come up with a good database design for the following problem: We have an application where users can create objects (such as notes) and link those objects to other objects, projects or users. Projects consist of assigned objects and a set of users who can access the project. How should we model the database to efficiently query for all objects that a user has access to, as well as for all users that have access to a given object? Access to an object is granted if one or more of the following conditions is met : 1. The user is the creator of the object 2. The object has been linked (shared) with the user 3. The user is a member of a project that the object is assigned to 4. The object is directly or indirectly linked to another object that the user has access to according to rules 1-3. I consider using a graph database, since the amount of SQL queries when using a relational database would not be feasible. We would need recursive SQL queries. Expected data dimensions are: 100-500 users, 100 projects and 100000-999999 objects

I've tried to translate and implement the PostGres solution to the problem found here: PostgreSQL - retrieve all IDs in a tree for a given subnode , but I'm either programming it wrong, or misunderstanding the OP's table structure and how it differs from mine. For any given node in a tree, I want to be able to return the entire tree and understand, at least, direct vs. indirect relationships of each node in that tree with that passed node. The passed node could have more than one ancestor and more than one descendant. I won't necessarily have access to the root node. The picture used in another SO post linked above illustrates what I'm dealing with pretty well, so I'm going to re-use it here: Here's the data table I'm working with, where LogID is parent and LinekdLogID is child: | LogID | LinkedLogID | | -----: | -----------: | | 1 | 6 | | 1 | 5 | | 6 | 4 | | 6 | 8 | | 6 | 11 | | 11 | 7 | | 7 | 10 | | 5 | 12 | | 12 | 2 | | 2 | 3 | | 14 | 17 | **The problem I have is that when I pass a node that has no descendants, I don't receive anything.** Passing node 10 for example (you could equate it to node #3 in the picture). The code does seem to work when I pass a node that is an ancestor to another node. **Additionally, I need to be able to tell, for each node in the tree whether it's a direct parent or child of the passed node.** Below is the code I've translated using the above post: DECLARE @passedLogId INT = '10'; --Problem node WITH ancestors(parent) as ( SELECT LogID[Parent] FROM Log_LinkedLogs WHERE LogID = @passedLogId UNION ALL SELECT t.LogID[Parent] FROM Log_LinkedLogs t INNER JOIN ancestors a ON t.LinkedLogID = a.parent ), descendants (LogID) AS ( SELECT parent as LogID from ancestors UNION ALL SELECT t.LinkedLogID FROM Log_LinkedLogs t INNER JOIN descendants as d ON t.LogID = d.LogID ) SELECT DISTINCT LogID FROM descendants *Edit - Simplified table by changing node IDs to match picture.

I'm personally interested in data structures and with knowing that rowstore indexes are stored with a B-Tree behind them, I'm always curious in trying to visualize what those B-Trees look like for a given table/index. Is there a way to correlate or expose the number of nodes in a B-Tree for a given rowstore index via the execution plan, statistics, operator properties, index / table properties etc? Furthermore can I do the same to determine the number of nodes traversed for a particular query? Bonus points if there's a way to determine the number of leaf nodes in the B-Tree too.

I need some quality sources - books, websites etc. - to educate myself about putting multiple hierarchy trees in a single table using SQL. I'm looking for some good theoretical and practical information.

Is there a way to traverse tree data in SQL? I know about connect by in Oracle, but is there another way to do this in other SQL implementations? I'm asking because using connect by is easier than writing a loop or recursive function to run the query for each result. Since some people seem to be confused by the phrase "tree data" I will explain further: What I mean is with regards to tables which have a parent_id or similar field which contains a primary key from another row in the same table. The question comes from an experience where I was working with data stored in this way in an Oracle database and knew that the connect by isn't implemented in other DBMSs. If one were to use standard SQL, one would have to create a new table alias for each parent one would want to go up. This could easily get out of hand.

I have a menu table in PostgreSQL 13 like this: -- Drop table -- DROP TABLE public.menu_resource; CREATE TABLE public.menu_resource ( id int4 NOT NULL GENERATED ALWAYS AS IDENTITY, "name" varchar NOT NULL, res_type int4 NOT NULL, created_time int8 NOT NULL, updated_time int8 NOT NULL, remark varchar NULL, "path" varchar NOT NULL, parent_id int4 NOT NULL, component varchar NULL, sort int4 NOT NULL, name_zh varchar NOT NULL, tree_id_path varchar NULL, CONSTRAINT auth_resource_id_seq PRIMARY KEY (id) ); and the menu data look like this with a tree structure that marked by parent_id and id: INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(13, 'job', 1, 1632389739938, 1632389739938, NULL, '/app/job', 1, NULL, 2, '求职管理', '5-1-13'); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(14, 'interview', 1, 1632389739938, 1632389739938, NULL, '/app/job/interview', 13, NULL, 2, '面试列表', '5-1-13-14'); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(15, 'menu', 1, 1632389739938, 1632389739938, NULL, '/privilege/menu', 2, './permission/menu', 0, '菜单列表', NULL); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(3, 'cruise', 1, 1632389739938, 1632389739938, NULL, '/app/cruise', 1, NULL, 2, 'Cruise', NULL); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(5, '系统菜单', 1, 1632389739938, 1632389739938, NULL, '/demo', 0, NULL, 0, '系统菜单', NULL); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(4, 'channel', 1, 1632389739938, 1632389739938, NULL, '/app/cruise/channel', 3, './apps/cruise/channel', 3, '频道', NULL); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(1, 'app', 1, 1632389739938, 1632389739938, NULL, '/app', 5, NULL, 1, '应用', NULL); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(2, 'privilege', 1, 1632389739938, 1632389739938, NULL, '/privilege', 5, NULL, 4, '权限管理', NULL); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(6, 'role', 1, 1632389739938, 1632389739938, NULL, '/privilege/role', 2, './permission/role', 5, '角色列表', NULL); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(7, 'article', 1, 1632389739938, 1632389739938, NULL, '/app/cruise/article', 3, './apps/cruise/article', 3, '文章', NULL); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(8, 'user', 1, 1632389739938, 1632389739938, NULL, '/privilege/user', 2, './permission/user', 5, '用户列表', NULL); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(9, 'welcome', 1, 1632389739938, 1632389739938, NULL, '/welcome', 5, './Welcome', 0, '欢迎', NULL); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(10, 'overview', 1, 1632389739938, 1632389739938, NULL, '/app/overview', 1, NULL, 2, '应用概览', NULL); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(11, 'product', 1, 1632389739938, 1632389739938, NULL, '/app/overview/product', 10, NULL, 2, '产品列表', NULL); INSERT INTO public.menu_resource (id, "name", res_type, created_time, updated_time, remark, "path", parent_id, component, sort, name_zh, tree_id_path) VALUES(12, 'list', 1, 1632389739938, 1632389739938, NULL, '/app/overview/list', 10, NULL, 2, '应用列表', NULL); now I want to generate the full path of the parent-child id, for example, the tree_id_path for job may look like this: 5-1-13, the interview may look like 5-1-13-14. what should I do to make it work like this?

Let's assume you have a nodes tables like this: CREATE TABLE nodes ( node serial PRIMARY KEY, parent integer NULL REFERENCES nodes(node), ts timestamp NOT NULL DEFAULT now() ); It represents a standard node-like tree structure with root nodes at the top and several child nodes dangling from root nodes or other child nodes. Let us insert a couple of example values: INSERT INTO nodes (parent) VALUES (NULL), (NULL), (NULL), (NULL) , (1), (1), (1), (1), (6), (1), (6) , (9), (6), (6), (3), (3), (3), (15); Now I want to retrieve the first 10 root nodes and all their children up to a depth of 4: WITH RECURSIVE node_rec AS ( (SELECT 1 AS depth, * FROM nodes WHERE parent IS NULL LIMIT 10) UNION ALL SELECT depth + 1, n.* FROM nodes AS n JOIN node_rec ON (n.parent = node_rec.node) WHERE depth < 4 ) SELECT * FROM node_rec; This works great and gives me the following result: depth | node | parent -------+------+-------- 1 | 1 | 1 | 2 | 1 | 3 | 1 | 4 | 2 | 5 | 1 2 | 6 | 1 2 | 7 | 1 2 | 8 | 1 2 | 10 | 1 2 | 15 | 3 2 | 16 | 3 2 | 17 | 3 3 | 9 | 6 3 | 11 | 6 3 | 13 | 6 3 | 14 | 6 3 | 18 | 15 4 | 12 | 9 As you might have noticed, there's no ORDER BY clause, so the order is not defined. The order you see here is from root nodes to deeper nodes. **How would I order the results as they would appear in an expanded tree view, as you can see from the example picture below?** I basically want the child nodes to be placed right after their corresponding parent node. If two or more child nodes have the same parent node, I want them to be sorted by their timestamp. Based on the example above, here's the desired output order that I'm trying to achieve: depth | node | parent | ts -------+------+--------+--------- 1 | 1 | | 2014-01-01 00:00:00 2 | 5 | 1 | 2014-01-01 00:10:00 2 | 6 | 1 | 2014-01-01 00:20:00 3 | 9 | 6 | 2014-01-01 00:25:00 4 | 12 | 9 | 2014-01-01 00:27:00 3 | 11 | 6 | 2014-01-01 00:26:00 3 | 13 | 6 | 2014-01-01 00:30:00 3 | 14 | 6 | 2014-01-01 00:36:00 2 | 7 | 1 | 2014-01-01 00:21:00 2 | 8 | 1 | 2014-01-01 00:22:00 2 | 10 | 1 | 2014-01-01 00:23:00 1 | 2 | | 2014-01-01 00:08:00 1 | 3 | | 2014-01-01 00:09:00 2 | 15 | 3 | 2014-01-01 10:00:00 3 | 18 | 15 | 2014-01-01 11:05:00 2 | 16 | 3 | 2014-01-01 11:00:00 2 | 17 | 3 | 2014-01-01 12:00:00 1 | 4 | | 2014-01-01 00:10:00