Geek Answers Handbook

Postgres hierarchy - counting levels of children and sorting by date of children or grandchildren

I would like to know how to write a postgres subquery so that the following example table returns what I need.

id   parent_id    postdate

1    　　-1      2015-03-10 
2 　　　　1      2015-03-11  (child level 1)
3　　　　 1      2015-03-12  (child level 1)
4　　　　 3      2015-03-13  (child level 2)
5　　　　-1      2015-03-14
6　　　　-1      2015-03-15
7　　　　 6      2015-03-16  (child level 1)

If I want to sort all root identifiers using child level 1 with counting children from the parent, the output will be something like this:

id    count　     date
6　　　2　　　  2015-03-15
1　　　4　　　  2015-03-10
5　　　1　　　  2015-03-14

. "" - . , № 5 , # 6 child (id # 7) , . id # 5 , , . "" - ( 1- ), ( 2- ) (). , id # 2, # 3, # 4 id # 1, id # 1 4.

:

SELECT p1.id,count(p1.id),p1.postdate
 FROM mytable p1
     LEFT JOIN mytable c1 ON c1.parent_id = p1.id AND p1.parent_id = -1
     LEFT JOIN mytable c2 ON c2.parent_id = c1.id AND p1.parent_id = -1
GROUP BY p1.id,c1.postdate,p1.postdate
ORDER by c1.postdate DESC,p1.postdate DESC
+4
4
create table mytable ( id serial primary key, parent_id int references mytable, postdate date );
create index mytable_parent_id_idx on mytable (parent_id);
insert into mytable (id, parent_id, postdate) values (1, null, '2015-03-10');
insert into mytable (id, parent_id, postdate) values (2, 1, '2015-03-11');
insert into mytable (id, parent_id, postdate) values (3, 1, '2015-03-12');
insert into mytable (id, parent_id, postdate) values (4, 3, '2015-03-13');
insert into mytable (id, parent_id, postdate) values (5, null, '2015-03-14');
insert into mytable (id, parent_id, postdate) values (6, null, '2015-03-15');
insert into mytable (id, parent_id, postdate) values (7, 6, '2015-03-16');

with recursive recu as (
  select id as parent, id as root, null::date as child_postdate
    from mytable
   where parent_id is null
   union all
  select r.parent, mytable.id, mytable.postdate
    from recu r
    join mytable
      on parent_id = r.root
)
select m.id, c.cnt, m.postdate, c.max_child_date
  from mytable m
  join ( select parent, count(*) as cnt, max(child_postdate) as max_child_date
           from recu
          group by parent
       ) c on c.parent = m.id
 order by c.max_child_date desc nulls last, m.postdate desc;
0

:

WITH RECURSIVE opa AS (
        SELECT id AS par
        , id AS moi
        FROM the_tree
        WHERE parent_id IS NULL
        UNION ALL
        SELECT o.par AS par
        , t.id AS moi
        FROM opa o
        JOIN the_tree t ON t.parent_id = o.moi
        )
SELECT t.id
        , c.cnt
        , t.postdate
FROM the_tree t
JOIN ( SELECT par, COUNT(*) AS cnt
        FROM opa o
        GROUP BY par
        ) c ON c.par = t.id
ORDER BY t.id
        ;

UPDATE (, OP maxdate )

        -- The same, but also select the postdate
        -- --------------------------------------
WITH RECURSIVE opa AS (
        SELECT id AS par
        , id AS moi
        , postdate AS postdate
        FROM the_tree
        WHERE parent_id IS NULL
        UNION ALL
        SELECT o.par AS par
        , t.id AS moi
        -- , GREATEST(o.postdate,t.postdate) AS postdate
        , t.postdate AS postdate
        FROM opa o
        JOIN the_tree t ON t.parent_id = o.moi
        )
SELECT t.id
        , c.cnt
        , t.postdate
        , c.maxdate
FROM the_tree t
JOIN ( SELECT par, COUNT(*) AS cnt
        , MAX(o.postdate) AS maxdate -- and obtain the max()
        FROM opa o
        GROUP BY par
        ) c ON c.par = t.id
ORDER BY c.maxdate, t.id
        ;
+1
select
 p.id,
 (1+c.n) as parent_post_plus_number_of_subposts,
 p.postdate
from 
    table as p
inner join

    (
    select
    parent_id, count(*) as n, max(postdate) as _postdate
    from table
    group by parent_id
    ) as c

on p.id = c.parent_id
where p.parent_id = -1
order by c._postdate desc
0

Having looked at each code, I created the necessary for the subquery. I can use PHP to change the case when code depending on the user's sorting choice. For example, the code below will sort the root nodes based on the first level child level.

with recursive cte as (
select id as parent, id as root, null::timestamp as child_postdate,0 as depth
from mytable
where parent_id = -1
union all
select r.parent, mytable.id, mytable.postdate,depth+1
from cte r
join mytable
  on parent_id = r.root
)
select m.id, c.cnt, m.postdate
from ssf.dtb_021 m
join ( select parent, count(*) as cnt, max(child_postdate) as max_child_date,depth
       from cte 
      group by parent,depth
     ) c on c.parent = m.id
order by 
  case
    when depth=2 then 1
    when depth=1 then 2
    else 0
  end DESC,
c.max_child_date desc nulls last, m.postdate desc;
0
source

More articles:


All Articles