Limit number of results in nested query

I have a table called comments with the following columns:

| id | user_id | post_id | parent_id | text |

I have this setup for a nested comment system, like Disqus:

http://ift.tt/2d4gd5R

If the comment has no parent comment, parent_id will be 0. But if a comment has a parent, the parent_id will be the id of the parent comment.

In my Comments.php model, I have this relationship:

public function children()
{
    return $this->hasMany('App\Comment', 'parent_id', 'id')->with('children');
}

Now, if I query the comments:

$comments = Comment::where('post_id', 1)
    ->where('parent_id', 0)
    ->with('children')
    ->get();

The output will look something like this:

[
    {
        "id": 5,
        "user_id": "2",
        "post_id": "1",
        "parent_id": "0",
        "text": "Text",
        "children": [
            {
                "id": 7,
                "user_id": "1",
                "post_id": "1",
                "parent_id": "5",
                "text": "Text",
                "children": [
                    {
                        "id": 8,
                        "user_id": "3",
                        "post_id": "1",
                        "parent_id": "7",
                        "text": "Text",
                        "children": [

                        ]
                    },
                    {
                        "id": 11,
                        "user_id": "3",
                        "post_id": "1",
                        "parent_id": "7",
                        "text": "Text",
                        "children": [

                        ]
                    },
                ]
            },
            {
                "id": 9,
                "user_id": "1",
                "post_id": "1",
                "parent_id": "5",
                "text": "Text",
                "children": [

                ]
            }
            ,
            {
                "id": 10,
                "user_id": "1",
                "post_id": "1",
                "parent_id": "5",
                "text": "Text",
                "children": [

                ]
            }
        ]
    },
    {
        "id": 6,
        "user_id": "3",
        "post_id": "1",
        "parent_id": "0",
        "text": "Text",
        "children": [

        ]
    }
]

Or, in simpler terms:

- 5
-- 7
--- 8
--- 11
-- 9
-- 10
- 6

What I want to do is limit the number of results returned per depth.

So, for example, how can I change my query/code such that it returns:

• Ten top-level results (parent_id of 0)
• Two level-1 results
• One level-2 result

So in the end, the query should output the comments like this:

- 5
-- 7
--- 8
-- 9
- 6

As you can see, there are two top-level results (up to 10 allowed), two level-1 results, and one level-2 result.

How can I do this?

via Chebli Mohamed