When paginating by parents, display their children

Question

I have a resultset with parent and child rows. (Child rows never have childs). I need to do pagination on it (considering the sort) so that:

First need to select only the parents on the pagination page (for example, when page size = 10, it must contain <= 10 parent rows), and then "stick" to them the childs of those parents who are in the pagination page.

The source resultset looks like:

+----+-----------+-------------+
| ID | PARENT_ID | SORT_COLUMN |
+----+-----------+-------------+
|  1 |           | y           |
|  2 |         1 | z           |
|  3 |           | u           |
|  4 |           | q           |
|  5 |         4 | o           |
|  6 |         4 | p           |
|  7 |           | c           |
+----+-----------+-------------+

The ~desired result:

+----+-----------+-------------+----+----------+
| ID | PARENT_ID | SORT_COLUMN | RN | RN_CHILD |
+----+-----------+-------------+----+----------+
|  7 |           | c           |  1 |          |
|  4 |           | q           |  2 |          |
|  5 |         4 | o           |  2 |        1 |
|  6 |         4 | p           |  2 |        2 |
|  3 |           | u           |  3 |          |
|  1 |           | y           |  4 |          |
|  2 |         1 | z           |  4 |        1 |
+----+-----------+-------------+----+----------+

Now I am doing it this way:

with
  cte as
    (select 1 as id, null as parent_id, 'y' as sort_column from dual
     union all
     select 2 as id, 1 as parent_id, 'z' as sort_column from dual
     union all
     select 3 as id, null as parent_id, 'u' as sort_column from dual
     union all
     select 4 as id, null as parent_id, 'q' as sort_column from dual
     union all
     select 5 as id, 4 as parent_id, 'o' as sort_column from dual
     union all
     select 6 as id, 4 as parent_id, 'p' as sort_column from dual
     union all
     select 7 as id, null as parent_id, 'c' as sort_column from dual)
select 
  *
from
  (select
    t.*, 
    dense_rank() over (order by 
                         case when t.parent_id is null 
                           then 
                             t.sort_column 
                           else 
                             (select t2.sort_column from cte t2 where t2.id = t.parent_id) 
                         end) as RN,
    case 
      when parent_id is null 
        then
          null 
        else 
          row_number() over (partition by t.parent_id order by t.sort_column) 
    end as RN_CHILD
from cte t)
--where RN between :x and :y
order by RN, RN_CHILD nulls first

But I assume that this can be done without unnecessary extra access to the result set. (select t2.sort_column from cte t2 where t2.id = t.parent_id).

How to do it?

UPD: parents must be sorted by sort_column, and childs within parents also must be sorted by sort_column.

score 0 · Answer 1 · answered Aug 02 '19 at 18:19

First there is a trick with the values statement, second you can use a left join and coalesce to get what you want, like this:

with
  cte(id,parent_id,sort_column) as
    (
      VALUES 
        (1, null, 'y' ),
        (2, 1   , 'z' ),
        (3, null, 'u' ),
        (4, null, 'q' ),
        (5, 4   , 'o' ),
        (6, 4   , 'p' ),
        (7, null, 'c' )
    ),
  cte_branch as
  (
     SELECT coalesce(parent.id, cte.id) as branch_id, cte.id, cte.parent_id, cte.sort_column, 
            row_number over (partition by coalesce(parent.id, cte.id) order by cte.sort_column) as rn
     FROM cte
     left join cte parent on cte.parent_id = parent.id
  )
select * from cte_branch
order by rn, id nulls first

score 0 · Answer 2 · answered Aug 03 '19 at 05:51

You need to use DENSE_RANK for RN and ROW_NUMBER for CHILD_RN as following:

with
  cte(id,parent_id,sort_column) as
    ( 
        SELECT 1, null, 'y' FROM DUAL UNION ALL
        SELECT 2, 1   , 'z' FROM DUAL UNION ALL
        SELECT 3, null, 'u' FROM DUAL UNION ALL
        SELECT 4, null, 'q' FROM DUAL UNION ALL
        SELECT 5, 4   , 'o' FROM DUAL UNION ALL
        SELECT 6, 4   , 'p' FROM DUAL UNION ALL
        SELECT 7, null, 'c' FROM DUAL
    )
SELECT
    C.*,
    DENSE_RANK() OVER(
        ORDER BY
            (CASE
                WHEN C.PARENT_ID IS NOT NULL THEN C.PARENT_ID
                ELSE C.ID
            END) DESC NULLS FIRST
    ) AS RN,
    CASE
        WHEN C.PARENT_ID IS NOT NULL THEN ROW_NUMBER() OVER(
            PARTITION BY C.PARENT_ID
            ORDER BY
                C.ID
        )
    END AS CHILD_RN
FROM
    CTE C
ORDER BY
    RN;

Output:

        ID  PARENT_ID S         RN   CHILD_RN
---------- ---------- - ---------- ----------
         7            c          1           
         4            q          2           
         5          4 o          2          1
         6          4 p          2          2
         3            u          3           
         2          1 z          4          1
         1            y          4           

7 rows selected.

Cheers!!

archjkee · Accepted Answer · 2019-08-05T16:57:38.210

In my case, instead of extra access to the resultset, the connect_by_root clause can be used:

(also, I noticed that my original SQL contains a bug - the parents with the same sort_column value have the same RN value, this is fixed here)

with
  cte(id, parent_id, sort_column) as
    (select 1, null, 'y' from dual union all
     select 2, 1,    'z' from dual union all
     select 3, null, 'u' from dual union all
     select 4, null, 'q' from dual union all
     select 5, 4,    'o' from dual union all
     select 6, 4,    'p' from dual union all
     select 7, null, 'c' from dual)
select 
  *
from
  (select
    t.*, 
    case when t.parent_id is null
      then
        row_number() over (partition by parent_id order by t.sort_column)
      else
        dense_rank() over (order by connect_by_root sort_column)
    end as RN as RN,
    case 
      when parent_id is null 
        then
          null 
        else 
          row_number() over (partition by t.parent_id order by t.sort_column) 
    end as RN_CHILD
from cte t
connect by prior id = parent_id
start with parent_id is null)
--where RN between :x and :y
order by RN, RN_CHILD nulls first

When paginating by parents, display their children

3 Answers3