0

Tree table

node_Id tree_path::ltree
aaaa aaaa
bbbb aaaa . bbbb
cccc aaaa . bbbb . cccc
dddd aaaa . bbbb . dddd
eeee eeee
ffff eeee . ffff

Object table

object_id node_id_fk name type
1111 aaaa name1 node
2222 aaaa name2 node
3333 cccc name3 node
4444 dddd name4 node
5555 eeee name4 node
6666 eeee name6 node
7777 bbbb name7 node
8888 dddd name8 node
9999 dddd name9 node

What I want to achieve in two steps:

  1. Get all entries from the "Object table" for requested node_ids and all ancestor ids in the tree_path in the "Tree table".
  2. And then add a column requested_node_id to to response from (1) to show the requested node_id to the "Object table" entries.

For the 1. step, i think, the the query looks something like that

Only the duplicate entries are not shown.

So we get these entries for the requested nodeIds ('bbbb', 'dddd') from the "Object table"

object_id node_id_fk name type
1111 aaaa name1 node
2222 aaaa name2 node
7777 bbbb name7 node
1111 aaaa name1 node
2222 aaaa name2 node
4444 dddd name4 node
7777 bbbb name7 node
8888 dddd name8 node
9999 dddd name9 node

For the 2. step (show column with the requested node_Id) I don't know how the query :( the response should look like this:

object_id node_id_fk name type requested_node_id
1111 aaaa name1 node bbbb
2222 aaaa name2 node bbbb
7777 bbbb name7 node bbbb
1111 aaaa name1 node dddd
2222 aaaa name2 node dddd
4444 dddd name4 node dddd
7777 bbbb name7 node dddd
8888 dddd name8 node dddd
9999 dddd name9 node dddd

Unfortunately only shows the node_id from the "node table" and not the requested node id

I hope the question is clear, leet me know if not

asky
  • 1
  • 1
  • Are you forced to go through step 1 or is step 2 just what are you trying to achieve? – lemon Jan 15 '23 at 13:17
  • Also double check your values for the Tree table. It looks like there are some issues with original data. – lemon Jan 15 '23 at 13:25
  • Hi @lemon, its just **step 2** what I want as a result. Regarding the "tree_path" values of the tree table, they are combined with a period `aaaa.bbbb.cccc`, but the stackoverflow editor doesn't let me save it that way – asky Jan 15 '23 at 14:28
  • I see `aaa` and `aaaa`, `dddd` and `ddddd` as two distinct values for each couple, was that intended? I see duplicate records in step 1 output, was that intended too? – lemon Jan 15 '23 at 14:40
  • The recursive fetch portion of your question (step 1) can be solved with this technique : https://stackoverflow.com/questions/68317916/get-all-ancestors-of-a-child-in-postgres-recursive-query – phatfingers Jan 15 '23 at 15:10
  • @lemon the `aaa` and `ddddd` should be the `aaaa` & `dddd`. My fault. For the output I would also like to have the duplicate entries – asky Jan 15 '23 at 15:56

0 Answers0