Using CTE (Common Table Expression) to achieve lazy loading and querying infinite hierarchical data structure

This article focuses on querying and implementing lazy loading for an infinite hierarchical data structure, which is commonly encountered in social media platforms that offer comment functionalities. Users can reply to specific comments, generating multi-level comments. If the system does not have restrictions, it may lead to an infinite depth of comments. This article records how to query and implement lazy loading for such infinite hierarchical data structure.

This structure can store comments and their hierarchical relationships. The comment_id is used to identify each comment, while the parent_id indicates which parent comment the comment is replying to.

Lazy loading is implemented on the frontend, while the backend is responsible for sequentially providing corresponding data. By utilizing the OFFSET-FETCH pagination technique, the backend can provide the desired data in the specified order.

e.g.

SQL
 OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY    

To retrieve data starting from the 0th record and fetching the next two records is equivalent to obtaining the 1st and 2nd records (modifiable as per requirements).

When sorting the comments based on the creation time, we can retrieve data in the correct order to resolve the issue of lazy loading. However, a problem arises with the creation time of child comments not always being in chronological order. This means that the oldest comment at the first level might have newer child comments. If we strictly sort based on creation time and return the latest 10 comments, the frontend might receive the newest child comment without getting the corresponding parent comment from the first level, causing display issues on the interface.

To address this issue, the data retrieval approach involves pagination and sorting based on the creation time of the first-level comments. It fetches the latest 10 first-level comments along with all their corresponding child comments. This ensures that there will be no mismatch between child comments and their respective parent comments, eliminating any problems arising from child comments not corresponding to their parent comments.

However, due to the possibility of having an infinite number of child comments, it is necessary to use CTE (Common Table Expression) to ensure that data from each level is accurately retrieved.

Assuming the table name is comment_history

e.g.

SQL
WITH CommentHierarchy AS (
    -- Anchor member definition
    SELECT
        comment_id,
        parent_id,
        message,
        created_time,
        created_user,
        1 AS Level
    FROM
        comment_history
    WHERE
        parent_id IS NULL -- Fetching top-level comments
		and comment_id in (
  select comment_id from comment_history
  where parent_id is null　
  order by created_time desc 
  OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY)
	

    UNION ALL

    -- Recursive member definition
    SELECT
        c.comment_id,
        c.parent_id,
        c.message,
        c.created_time,
        c.created_user,
        ch.Level + 1 AS Level
    FROM
        comment_history c
    INNER JOIN
        CommentHierarchy ch ON c.parent_id = ch.comment_id
)
SELECT 
    comment_id,
    parent_id,
    message,
    created_time,
    created_user,
	Level
FROM
    CommentHierarchy
ORDER BY
    created_time DESC  

Syntax Explanation:

Create the CommentHierarchy, first fetching the original data of comments where the condition is specified.

parent_id IS NULL (It represents the first level.) 

and

comment_id in (
  select comment_id from comment_history
  where parent_id is null　
  order by created_time desc 
  OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY)

In conjunction with lazy loading, fetch the latest 10 comments and create a new column "Level" with a value of 1 (representing first-level comments).

After obtaining the first level, perform a JOIN between the original data and CommentHierarchy, binding the relationship where the parent_id in the original data matches the comment_id in the CommentHierarchy.(Retrieve the second-level child comments using the comment_id from the first level.)

-- Recursive member definition
    SELECT
        c.comment_id,
        c.parent_id,
        c.message,
        c.created_time,
        c.created_user,
        ch.Level + 1 AS Level
    FROM
        comment_history c
    INNER JOIN
        CommentHierarchy ch ON c.parent_id = ch.comment_id

Continuously use the CommentHierarchy's comment_id to determine if there are child comments, obtaining each level of child comments in a recursive manner, and finally merging all the data together.

With the above approach, you can obtain all child comments of parent comments and label the level of each comment.

SQL

Alvin

Software engineer, interested in financial knowledge, health concepts, psychology, independent travel, and system design.

Recording tips for optimizing query performance through indexing, differences between clustered and non-clustered indexes.

As the system data grows increasingly large, it's common to encounter situations where database queries take too long. This article provides several methods for optimizing database query performance.

Retrieve the Most Recently Added Data in SQL

SQL-implement data partitioning (partition by)

Implement data partitioning using SQL partition by and compare the performance with LINQ.

SQL-merge into statement

This tutorial demonstrates how to implement a site search feature in a Next.js project using Fuse.js, including pre-building a JSON file and dynamically displaying content based on user input.

Recording tips for optimizing query performance through indexing, differences between clustered and non-clustered indexes.

As the system data grows increasingly large, it's common to encounter situations where database queries take too long. This article provides several methods for optimizing database query performance.

Retrieve the Most Recently Added Data in SQL

SQL-implement data partitioning (partition by)

Implement data partitioning using SQL partition by and compare the performance with LINQ.

SQL-merge into statement

This tutorial demonstrates how to implement a site search feature in a Next.js project using Fuse.js, including pre-building a JSON file and dynamically displaying content based on user input.