PostgreSQL – Postgres hierarchy – counting of sub-level, sorted by subkey or grandson

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)< br />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 ids by child level 1, and the number of child levels of the parent is, then the output will be like this

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

The child processes of root are sorted by postdate. The output “date” is the expiration date of the root. Even if id#5 has an updated postdate, the child of rootid#6 (id#7) also has the latest postdate, because it is based on the child’s postdate Sorted. id#5 does not have any children so it is just placed at the end, sorted by date.’count’ is the number of children (child 1 level), grandson (child 2 level) and self (root). For example, id#2 ,#3,#4 all belong to id#1, so for id#1, the count will be 4.

My current subquery so far:

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

< /div>

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;

I want to know how to write a postgres subquery so that the following table example will output 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< br />6　　　　-1 2015-03-15
7　　　　 6 2015-03-16 (child level 1)

If I want to sort all root ids by child level 1, and the parent The number of sub-levels of the level is, then the output will be like this

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

The output is sorted by postdate according to root’s child processes. The output “date” is the expiration date of the root. Even if id#5 has an updated postdate, rootid#6’s children (id#7) also have the latest postdate, because it is sorted by the postdate of the child. id#5 does not have any children so it is just placed at the end, sorted by date.’count’ is the child (child 1 level), grandson (child 2 level) and self (root ). For example, id#2,#3,#4 belong to id#1, so for id#1, the count will be 4.

My current subquery so far:

p>

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

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< br /> 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 = mi d
 order by c.max_child_date desc nulls last, m.postdate desc;