The table contains the fields id , parent_id , depth .

Suppose there are such data:

1, nil, 1 2, 1, 2 3, 1, 2 4, 1, 2 5, 4, 3 6, 4, 3

I need to choose a tree with a limit on the choice of the number of children and levels ( depth ). That is, to get for example such a result ( limit child 1, depth <= 3 )

tree id s:

 1 4 5

Is it possible to make sql query in postgresql?

  • ru.stackoverflow.com/search?q=%5Bpostgresql%5D+recursive see examples of recursive queries. in the second part, check the level of nesting having a corresponding. Field - Mike
  • the fact is that in recursive queries you cannot set a limit on the number of children - Vitaly Igorevich
  • Well, if you wish, everything can be done. the question is why in the example you took record 4 at the second level (with the maximum id among children), and record 5 at the third level (with the minimum). By what criterion you choose which branch to go for - Mike
  • I want to make a commenting system like reddit. in the example, I took an arbitrary sequence, which demonstrates the real use case if you use sorting by the score field - Vitaly Igorevich
  • I do not know what reddit is and at the moment I don’t want to know. Give the data by which one could unequivocally say why such a path was chosen. And by the way, what is depth, is it really stored in the source table? It has to be dynamically calculated ... - Mike

1 answer 1

Unfortunately, according to the data in the question it is impossible to understand why exactly those records that are given in the expected result were chosen. It is also not clear why the depth field is present in the table, because the depth of data inheritance must be determined dynamically, based on the inheritance. Suppose (according to your comments) that there is a score field in the table and that the heir with the maximum score should be considered the "first" heir. Also suppose that in the source table there is no depth field and we need to define it. The name of the source table is TabX . Then the query is obtained as follows:

 with Recursive Q(id,depth) as( select id, 1 from TabX where parent_id is null union all select Src.id, Q.depth+1 from Q, Src where Q.depth<3 and Src.parent_id=Q.id ), Src(id,parent_id) as( select * from ( select id,parent_id, row_number() over(partition by parent_id order by score desc) child_num from TabX ) A where child_num<=1 ) select * from Q

First, in the Src block, we number all the child records with one parent in a row and select only the first (in the given sorting) numbers from them. After that, in a recursive block we build a regular tree from these already filtered records. At the same time, not forgetting to consider the nesting level and in the condition of a recursive block, we stop the selection when the depth reaches the specified one.

WITH blocks are located a little not logical, because such a postrgesql requirement - the recursive block should be the first. But this does not prevent us from accessing from this block to the sample records written below.