iamsubingyawali's avatar

Fetching paginated comments with replies

Hi, I am building an API where I have comments and single level replies i.e. a comment can have replies, but replies can't have any replies. My implementation has a single comments table where replies and comments are identified by parent_id column.

I am trying to fetch all comments in a post such that, first a parent comment is fetched, and their replies are fetched and when replies for the first comment are finished, second comment is fetched.

Similar thing can be easily done if I want to fetch all the comments and their replies without pagination but what I want to do is, apply pagination to comments and count comments and replies while applying the pagination limit. For example, my pagination limit is 8, if I have 5 comments and 10 replies in first comment, I will fetch first comment and 7 replies in that comment in the first page and leave other replies and comments to be fetched on next page request.

I am currently using this eloquent query to fetch comments:

$comments = Comment::where('post_id', 1)->simplePaginate(8);

The problem with this query is, it fetches all comments and replies on their order of insertion. For example:

{
    "id": 1,
    "user_id": 2,
    "post_id": 1,
    "parent_id": null,
    "content": "Test Comment by me",
    "created_at": "2022-05-04T10:55:03.000000Z",
    "updated_at": "2022-05-04T10:55:03.000000Z",
},
{
    "id": 2,
    "user_id": 2,
    "post_id": 1,
    "parent_id": 1,
    "content": "Test Comment by me",
    "created_at": "2022-05-04T11:00:16.000000Z",
    "updated_at": "2022-05-04T11:00:16.000000Z",
},
{
    "id": 3,
    "user_id": 2,
    "post_id": 1,
    "parent_id": null,
    "content": "Test Comment by me",
    "created_at": "2022-05-04T11:00:23.000000Z",
    "updated_at": "2022-05-04T11:00:23.000000Z",
},
{
    "id": 4,
    "user_id": 2,
    "post_id": 1,
    "parent_id": null,
    "content": "Test Comment by me",
    "created_at": "2022-05-04T11:00:39.000000Z",
    "updated_at": "2022-05-04T11:00:39.000000Z",
},
{
    "id": 5,
    "user_id": 2,
    "post_id": 1,
    "parent_id": 1,
    "content": "Test Reply by me",
    "created_at": "2022-05-04T11:07:44.000000Z",
    "updated_at": "2022-05-04T11:07:44.000000Z",
},
{
    "id": 6,
    "user_id": 2,
    "post_id": 1,
    "parent_id": 2,
    "content": "Test Comment by me",
    "created_at": "2022-05-04T11:08:03.000000Z",
    "updated_at": "2022-05-04T11:08:03.000000Z",
},
{
    "id": 7,
    "user_id": 2,
    "post_id": 1,
    "parent_id": 1,
    "content": "Test Comment by me",
    "created_at": "2022-05-04T11:17:01.000000Z",
    "updated_at": "2022-05-04T11:17:01.000000Z",
},
{
    "id": 8,
    "user_id": 2,
    "post_id": 1,
    "parent_id": 1,
    "content": "Test Reply by me",
    "created_at": "2022-05-04T11:18:53.000000Z",
    "updated_at": "2022-05-04T11:18:53.000000Z",
}

What I want is like this:

{
    "id": 1,
    "user_id": 2,
    "post_id": 1,
    "parent_id": null,
    "content": "Test Comment by me",
    "created_at": "2022-05-04T10:55:03.000000Z",
    "updated_at": "2022-05-04T10:55:03.000000Z",
},
{
    "id": 2,
    "user_id": 2,
    "post_id": 1,
    "parent_id": 1,
    "content": "Test Comment by me",
    "created_at": "2022-05-04T11:00:16.000000Z",
    "updated_at": "2022-05-04T11:00:16.000000Z",
},
{
    "id": 5,
    "user_id": 2,
    "post_id": 1,
    "parent_id": 1,
    "content": "Test Reply by me",
    "created_at": "2022-05-04T11:07:44.000000Z",
    "updated_at": "2022-05-04T11:07:44.000000Z",
},
{
    "id": 7,
    "user_id": 2,
    "post_id": 1,
    "parent_id": 1,
    "content": "Test Comment by me",
    "created_at": "2022-05-04T11:17:01.000000Z",
    "updated_at": "2022-05-04T11:17:01.000000Z",
},
{
    "id": 8,
    "user_id": 2,
    "post_id": 1,
    "parent_id": 1,
    "content": "Test Reply by me",
    "created_at": "2022-05-04T11:18:53.000000Z",
    "updated_at": "2022-05-04T11:18:53.000000Z",
}
{
    "id": 3,
    "user_id": 2,
    "post_id": 1,
    "parent_id": null,
    "content": "Test Comment by me",
    "created_at": "2022-05-04T11:00:23.000000Z",
    "updated_at": "2022-05-04T11:00:23.000000Z",
},
{
    "id": 6,
    "user_id": 2,
    "post_id": 1,
    "parent_id": 3,
    "content": "Test Comment by me",
    "created_at": "2022-05-04T11:08:03.000000Z",
    "updated_at": "2022-05-04T11:08:03.000000Z",
},
{
    "id": 4,
    "user_id": 2,
    "post_id": 1,
    "parent_id": null,
    "content": "Test Comment by me",
    "created_at": "2022-05-04T11:00:39.000000Z",
    "updated_at": "2022-05-04T11:00:39.000000Z",
},

How do I structure my query to get this?

0 likes
13 replies
tykus's avatar

This seems like a very odd structure to fetch - are you going to need to nest the replies under their respective comments afterwards?

You could simply eager-load a replies relationship with each parent Comment:

$comments = Comment::with('replies')
    ->whereNull('parent_id')
    ->where('post_id', 1)
    ->simplePaginate(8);

The resulting structure will nest replies as a property of each Comment - and to my mind would be a better structure because you guarantee 8 parent comments per page regardless the number of replies each comment has.

iamsubingyawali's avatar

@tykus Yes I am going to nest the replies but I am not saying that I will fetch 8 parent comments instead, what I said is I will fetch 8 records containing comments and their replies. And I want to load another 8 records (comments and replies) on another page request.

tykus's avatar

@iamsubingyawali I understood the nature of the data you want - my comment about its oddness relates to the fact that you will (almost certainly) have no context for some pages which will be comprised of the replies only. Anyway, the second reply I shared solves for your desired structure.

iamsubingyawali's avatar

@tykus Okay, I realized what you were trying to say. With this approach, it would be very inefficient if there are many replies in a comment. How do I paginate many replies within a single comment along with all the comments efficiently?

tykus's avatar

@iamsubingyawali I don't know what you mean inefficient...

With this approach, it would be very inefficient if there are many replies in a comment.

And moreso here:

How do I paginate many replies within a single comment along with all the comments efficiently?

What does this structure look like?

iamsubingyawali's avatar

@tykus What I mean by inefficient is, let's say I have 100 replies inside a comment, if I fetch 100 replies and comments at once, response would be slow.

The structure I wanted is in my question. Unfortunately, your another answer below also doesn't give that structure.

But as you already said, my structure will not be feasible all the time, so I want to implement it differently where I want to fetch comments and replies, both paginated.

tykus's avatar

@iamsubingyawali

let's say I have 100 replies inside a comment, if I fetch 100 replies and comments at once, response would be slow.

Why would it be slow?

Unfortunately, your another answer below also doesn't give that structure.

Really; what are you getting instead? What is different from expected?

iamsubingyawali's avatar

@tykus

Why would it be slow?

I think it might be slow because, more data = more response size = more bandwidth consumption and it might impact application performance in slow or low bandwidth network scenarios.

Really; what are you getting instead? What is different from expected?

I am expecting to get the comments and their replies serially, but your answer doesn't make much difference than my query in the question. When I fetch comments, I am fetching the comments for a single post so, orderBy post_id doesn't have any impact.

jlrdw's avatar

@iamsubingyawali Why not do similar to forum, once a post is retrieved paginate the replies. Don't you only need one post at a time once retrieved?

Don't try to do everything in one big query.

  • Query and display post
  • Query selected post to view with replies (paginated)
iamsubingyawali's avatar

@jlrdw No. I am not trying to fetch everything in a big query. To make it clear, I have a posts table and a comments table. First, I fetch a post content and then only I will fetch comments. But the problem is while fetching the comments. The number of comments might be in 100s or 1000s including comments and replies. So, fetching 1000s of records at once without pagination might have an impact on application performance.

jlrdw's avatar

@iamsubingyawali

So, fetching 1000s of records at once without pagination might have an impact on application performance.

Of course you need to paginate. I fixed other reply, I thought it was understood to paginate replies anyway.

tykus's avatar

If you must have a flat structure, then:

$comments = Comment::where('post_id', 1)
    ->orderBy('post_id')
    ->orderBy('created_at')
    ->simplePaginate(8);

Please or to participate in this conversation.