Paginate only root nodes but still show child nodes when recursively retrieving child nodes in mysql

Question

I am implementing pagination of a tree structure table in mysql. I have a Table: account with recursive association, an account can be boss of other account. A self relationship of kind (SuperVisor (1)- SuperVisee (∞) ).

Query to Create Table:

CREATE TABLE account (
   AccountId char(36) NOT NULL DEFAULT '' COMMENT 'key',
   AccountParentId char(36) DEFAULT NULL COMMENT 'Id root node',
   CreatedDate datetime DEFAULT NULL COMMENT 'created date',
   PRIMARY KEY (AccountId)
)
ALTER TABLE account
ADD CONSTRAINT FK_account_AccountParentId FOREIGN KEY (AccountParentId)
REFERENCES account (AccountId);

I have inserted a set of tuples (Query):

INSERT INTO account VALUES 
 ("1", "null", "2001-01-02"),
 ("2", "1", "2001-02-02"),
 ("3", "2", "2001-03-02"),    
 ("4", "null", "2001-04-02"),
 ("5", "4", "2001-05-02"), 
 ("6", "null", "2001-06-02"),

The inserted rows has the following Tree-Hierarchical-Relationship:

 - 1
   - 2
     - 3
 - 4
   - 5
 - 6

I wrote a procedure to perform paging of account table data:

PROCEDURE Proc_Account_Pagination(IN p_PageNumber INT, IN p_PageSize INT)
BEGIN

CREATE TEMPORARY TABLE IF NOT EXISTS tbAccountOrginal AS
    WITH RECURSIVE AccountTree AS
    (
        SELECT AccountId, AccountParentId, CAST(CreatedDate AS CHAR(200)) AS CreatedDate, CAST(AccountId AS CHAR(200)) AS path,
        0 AS depth FROM view_account WHERE AccountParentId IS NULL
        UNION ALL
        SELECT
        va.AccountId,
        va.AccountParentId,
        AccountTree.CreatedDate,
        CONCAT(AccountTree.path, ",", va.AccountId),
        AccountTree.depth+1 FROM view_account va JOIN AccountTree ON AccountTree.AccountId=va.AccountParentId
    )
    SELECT ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS RowIndex,
    AccountId,
    AccountParentId,
    path,
    depth FROM AccountTree ORDER BY path;

  SELECT * FROM  tbAccountOrginal eo WHERE eo.RowIndex BETWEEN @IndexStart AND @IndexEnd;
  DROP TEMPORARY TABLE IF EXISTS tbAccountOrginal;

END

For example, I want when I select the number of records on 1 page is 3, the data displayed is 3 parent accounts and the child accounts of those 3 parent accounts. Child accounts are displayed under its parent account, the output of the above procedure is:

AccountId	AccountParentId	Path	Depth
1	null	1	0
2	1	1, 2	1
3	2	1, 2, 3	2

But I want to receive such a structure:

AccountId	AccountParentId	Path	Depth
1	null	1	0
2	1	1, 2	1
3	2	1, 2, 3	2
4	null	4	0
5	4	4, 5	1
6	null	6	0

I have tried adding WHERE AccountParentId IS NULL to the query to the AccountTree recursive table but the result shows up like this:

AccountId	AccountParentId	Path	Depth
1	null	1	0
4	null	4	0
6	null	6	0

I really hope everyone can help me. Thanks a lot.

Answer 1

Here is an example on how you can accomplish this.

setting up the data...

drop table if exists account;
create table account
(
    accountid       char(36) not null default '' comment 'key'
  , accountparentid char(36) default null comment 'Id root node'
  , createddate     datetime default null comment 'created date'
  , primary key (accountid)
);


INSERT INTO account VALUES 
 ("1", "null", "2001-01-02"),
 ("2", "1", "2001-02-02"),
 ("3", "2", "2001-03-02"),    
 ("4", "null", "2001-04-02"),
 ("5", "4", "2001-05-02"), 
 ("6", "null", "2001-06-02");


drop procedure if exists proc_account_pagination;

Because your paging based on the parent accounts only, you first need calculate the limit and offset of the records in question.

create procedure proc_account_pagination (in p_pagenumber int, in p_pagesize int)
begin
    
    declare v_offset int;
    set v_offset = (p_pagenumber - 1) * p_pagesize;

Then insert those records into a temp table, you can also use an in-line sub query, dealers choice here... (note I'm using ..where accountparentid = 'null' because you're inserting them in your example as char, fixing the data types is recommended :) )

    drop temporary table if exists temp_page;
    create temporary table temp_page
    select
        accountid
      , accountparentid
      , cast(createddate as char(200)) as createddate
      , cast(accountid as char(200))   as path
      , 0                              as depth
    from account
    where accountparentid = 'null'
    limit p_pagesize offset v_offset;

Now run a similar recursive cte to gather the children

    with recursive accounttree
    as
    (
            select
                accountid
              , accountparentid
              , createddate
              , path
              , depth
            from temp_page
            union all
            select
                va.accountid
              , va.accountparentid
              , accounttree.createddate
              , concat(accounttree.path, ',', va.accountid)
              , accounttree.depth + 1
            from account va
                join accounttree
                    on accounttree.accountid = va.accountparentid
    )
    select
        row_number() over (
            order by
            createddate desc) as rowindex
      , accountid
      , accountparentid
      , path
      , depth
    from accounttree
        order by
        path;

end;

And finally test it, good luck!

call proc_account_pagination(1, 3);