I have a nested comment system that looks like this:
- Comment 1
- Comment 2
- Comment 3
- Comment 4
- Comment 5
- Comment 6
- Comment 7
- Comment 8
- Comment 9
- Comment 10
- Comment 11
etc
All of the comments are stored in a Comments
table, where each comment keeps track of its parent comment (parent_id
) and its depth:
----------------------------------------
| id | parent_id | depth | message |
----------------------------------------
| 1 | 0 | 0 | Comment 1 |
| 2 | 1 | 1 | Comment 2 |
| 3 | 2 | 2 | Comment 3 |
| 4 | 1 | 1 | Comment 4 |
| 5 | 1 | 1 | Comment 5 |
| 6 | 0 | 0 | Comment 6 |
| 7 | 6 | 1 | Comment 7 |
| 8 | 7 | 2 | Comment 8 |
| 9 | 8 | 3 | Comment 9 |
| 10 | 7 | 2 | Comment 10 |
| 11 | 6 | 1 | Comment 11 |
----------------------------------------
I have this relationship in my Comment.php
model class, which gets all children of a comment:
public function children() {
return $this->hasMany('App\Comment', 'parent_id', 'id');
}
My questions are:
-
How do I create a query to get all comments in a nested JSON format?
-
Additionally, I want to limit the number of results retrieved per depth. For example, I want to only retrieve 5 parent comments (depth of 0), 3 comments at depth 1, and 2 comments at depth 2. How can I add these limits to the query?
Here is the query that I started, but this only retrieves comments at a depth of 0 (i.e. only parent comments) and limits it to 5:
$comments = Comment::select('comments.*')
->where('parent_id', 0)
->take(5);
How do I continue from here?
via johbnahri