Recursive query in Oracle – length of chain

i’ve got table with some posts. Post have fields: post_id (key) and parent_post_id (can be null). ( If post A is replying to post B then A.parent_post_id = B.post_id). My problem is: write query to oracle db to get ‘chain length’ for every post. I mean: for every post_id i want to know how many nested child there are.(So it’s more like tree size, but chain example would be helpful too) I have tried some recursive queries but i this case i helpless.

Fe. if arrow ( <-) means is parent. For A <- B <- C <- D I want to get:

A 3
B 2
C 1
D 0

Answer

Hierarchical queries are made for your case. I slightly extended your tree in the following way:

A <- B <- C  <- D
     B <- C1 <- D1 
          C1 <- D2 

If I understand you correctly, the following are the desired results:

A   6
B   5
C   1
C1  2
D   0
D1  0
D2  0

Here is the query:

with source_data as (
  select 'A' post_id, null parent_post_id from dual
  union
  select 'B' post_id, 'A' parent_post_id from dual
  union
  select 'C' post_id, 'B' parent_post_id from dual
  union
  select 'D' post_id, 'C' parent_post_id from dual
  union
  select 'C1' post_id, 'B' parent_post_id from dual
  union
  select 'D1' post_id, 'C1' parent_post_id from dual
  union
  select 'D2' post_id, 'C1' parent_post_id from dual
),
chains as (
  select connect_by_root post_id post_id, post_id child_post_id
  from source_data
  connect by nocycle prior post_id = parent_post_id
)
select post_id, count(1) - 1 chain_length
from   chains
group by post_id
order by 1

Hope this helps.