samedi 6 mai 2017

Nested comments query with limits

I have a nested comment system that essentially 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), like so:

--------------------------------
| id  | parent_id |    message |
--------------------------------
|   1 |         0 |  Comment 1 |
|   2 |         1 |  Comment 2 |
|   3 |         2 |  Comment 3 |
|   4 |         1 |  Comment 4 |
|   5 |         1 |  Comment 5 |
|   6 |         0 |  Comment 6 |
|   7 |         6 |  Comment 7 |
|   8 |         7 |  Comment 8 |
|   9 |         8 |  Comment 9 |
|  10 |         7 | Comment 10 |
|  11 |         6 | Comment 11 |
--------------------------------

I have this relationship in my Comment.php model class:

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

Which gets all children of a comment.

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 Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire