NestJS nodejs load nested comments in one query with relations?

You're basically using an Adjacency list Tree.

Adjacency list is a simple model with self-referencing. The benefit of this approach is simplicity, BUT the drawback is that you can't handle deep trees with that.

There's a recursive way of doing it with Adjacency list, but it doesn't work with MySQL.

Solution is to use another type of tree. Other possible trees are:

• Nested set: Its very efficient for reads, but bad for writes. You cannot have multiple roots in nested set.
• Materialized Path: (also called Path Enumeration) is simple and effective.
• Closure table: stores relations between parent and child in a separate table. Is efficient in both reads and writes (Updating or removing a component's parent has not been implemented yet)

@Entity()
@Tree("nested-set") // or @Tree("materialized-path") or @Tree("closure-table")
export class Category {

    @PrimaryGeneratedColumn()
    id: number;

    @TreeChildren()
    children: Category[];

    @TreeParent()
    parent: Category;
}

To load a tree use:

const manager = getManager();
const trees = await manager.getTreeRepository(Category).findTrees();

After you get a tree repository, you can use next functions: findTrees(), findRoots(), findDescendants(), findDescendantsTree() and others. See documentation for more.

Learn more about different type of trees: Models for hierarchical data

As Gabriel said, other data models are better to do what you want performance wise. Still if you can't change the database design, you can use alternatives (which are less performant or pretty, but what works in production is all that matters in the end).

As you set the Lead value in your LeadComment, I can suggest that you set this value also on replies on the root comment on reply creation (should be easy in the code). This way you can fetch all comments on your customer in one query (including the replies).

const lead = await leadRepository.findOne(id);
const comments = await commentRepository.find({lead});

Of course, you will have to run a SQL batch to populate the missing column values, but it is a one time thing, and once your codebase is patched as well you won't have to run anything afterwards. And it doesn't change the structure of your database (just the way data is populated).

Then you can build in nodejs the whole stuff (lists of replies). To get the "root" comment, simply filter by comment that are not replies (that don't have parents). If you just want the root comments from the database you can even change the query to only these ones (with parentComment null in SQL column).

function sortComment(c1: LeadComment , c2: LeadComment ): number {
    if (c1.created_at.getTime() > c2.created_at.getTime()) {
    return 1;
    }
    if (c1.created_at.getTime() < c2.created_at.getTime()) {
        return -1;
    }
    return 0;
}
const rootComments = comments
    .filter(c => !c.parentComment)
    .sort(sortComment);

Then you can get replies on the rootComments and build the whole list recursively in node.

function buildCommentList(currentList: LeadComment[], allComments: LeadComment[]): LeadComment[] {
    const lastComment = currentList[currentList.length - 1];
    const childComments = allComments
        .filter(c => c.parentComment?.id === lastComment.id)
        .sort(sortComment);
    if (childComments.length === 0) {
        return currentList;
    }
    const childLists = childComments.flatMap(c => buildCommentList([c], allComments));
    return [...currentList, ...childLists];
}

const listsOfComments = rootComments.map(r => buildCommentList([r], comments));

There are probably more optimized ways to compute these lists, this is for me one of the simplest that can be made.

Depending on the number of comments it can get slow (you can limit results by timestamp and number for instance so that it should be good enough?) so beware, don't fetch the universe of comments on a "Justin Bieber" Lead that get many comments...