how to eager loading nested relationships in Laravel with custom query?

Question

I have a comments table, with theses fields: id, body, and parent_id. comments can have subcomments, that's why it is using the parent_id field, I would like to know how I can achieve this nested result:

[
    {
        "id": 1,
        "body": "test",
        "parent_id": null,
        "comments": [
            {
                "id": 2,
                "body": "test",
                "parent_id": 1,
                "comments": [
                    {
                        "id": 3,
                        "body": "test",
                        "parent_id": 2
                    },
                    {
                        "id": 4,
                        "body": "test",
                        "parent_id": 2
                    }
                ]
            },
            {
                "id": 5,
                "body": "test",
                "parent_id": 1,
                "comments": []
            }
        ]
    },
    {
       "id": 6,
       "body": "test",
       "parent_id": null,
       "comments": []
    }       
]

Without using eloquent eager loading(with()), just using the query builder, thank you.

Answer 1

Since the possible answer could be too long, I am posting it directly as an answer. You could achieve this by creating a recursive method in your controller like below:

public function commentsLoop($comments, $parent = null)
{
    $result = [];

    foreach ($comments as $comment) {
        if ($comment['parent_id'] === $parent) {
            $subComment = commentsLoop($comments, $comment['id']);
            
            if ($subComment) {
                $comment['comments'] = $subComment;
            }

            $result[] = $comment;
        }
    }

    return $result;
}

And then, you can call it from your main method in your controller like below:

public function comments()
{
    $comments = DB::table('comments')->all()->toArray();
    
    return $this->commentsLoop($comments);
}

However, if you would use eloquent instead, creating a relationship to self in your model would be the answer.