Sunday, May 21, 2017

Nested comments with limits at each level

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:

  1. How do I create a query to get all comments in a nested JSON format?

  2. 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

Advertisement