7

I have this:

$commentReplies = Comment::whereIn('comment_parent_id', $CommentsIDs)
                                  ->take(2)->get();

Where $CommentsIDs is an array of 3 parent comment ids (1,2,3).

I am trying to retrieve 2 replies for each of the $commentsIDs if they exist. So a total of 6 replies (2 for each comment) should come back with the query, if the replies exist, nothing more. However, with take(2) in there, it limits the replies to 2, and we only get 2 replies for one of the comments. How can it be setup to get 2 replies for each of the comment IDs in the most efficient way, and how can they get rendered in the view with the correct nesting?

Something like:

Comment 1
--Comment 1 Reply 1 (load this)
--Comment 1 Reply 2 (load this)
--Comment 1 Reply 3 (don't load this)
--Comment 1 Reply 4 (don't load this)
Comment 2
--Comment 2 Reply 1 (load this)
--Comment 2 Reply 2 (load this)
--Comment 2 Reply 3 (don't load this)
Comment 3
(no replies, don't load anything)

Update:
Here is the Comment Model:

class Comment extends BaseModel {

 public function latestTwoComments()
 {
     return $this->hasMany('Comment','comment_parent_id')->latest()->nPerGroup('comment_parent_id', 2);
 }

}

Query:

$comments = Comment::with('latestTwoComments')->get();
dd(DB::getQueryLog());

// Result:
'query' => string 'select * from (select `comments`.*, @rank := IF(@group = comment_parent_id, @rank+1, 1) as rank_575b053fb57f8fab5bc86dd324b39b91, @group := comment_parent_id as group_575b053fb57f8fab5bc86dd324b39b91 from (SELECT @rank:=0, @group:=0) as vars, comments where `comments`.`deleted_at` is null order by `comment_parent_id` asc, `created_at` desc) as comments where `comments`.`deleted_at` is null and `rank_575b053fb57f8fab5bc86dd324b39b91` <= ? and `comments`.`comment_parent_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?'... (length=603)
Wonka
  • 8,244
  • 21
  • 73
  • 121
  • So far so good. As you can see the query takes only 2 comments per each `comment_parent_id`, so what's the problem? – Jarek Tkaczyk Oct 08 '14 at 14:47
  • Yes, it load 2 replies per comment, but for some reason it also loads all the comments and replies as well. And the same replies that are nested as 2, are also being retrieved from the database as comments (unnested). I was only trying to get what I need and not more. So if I have 40 comments, this query puts in 40 ids in the comment_parent_id (? ? and 38 more ?) area, so loads everything it seems still, in addition to the correct nesting. Just trying to get the parent comment, 2 nested replies for each, and that's it. – Wonka Oct 08 '14 at 14:58
  • That's different story my friend. If you need only parent comments, then (assuming `comment_parent_id` is nullable) do this: `Comment::whereNull('comment_parent_ud')->with('latestTwoComments')->get()` - it will load only parents and 2 children for each of them. – Jarek Tkaczyk Oct 08 '14 at 15:03
  • `reverse` after `get` works on the parent comments collection, so it doens't make sense. Latest comments are ordered by `created_at` desc, I don't know why 3 should be before 4 or whatever. You must work it out, you already have the code, the query that is executed, so it's all there. – Jarek Tkaczyk Oct 08 '14 at 16:53
  • Okay - I'll keep trying. Thank you for all your help and great articles on your site! – Wonka Oct 08 '14 at 18:55
  • I created a package for this: https://github.com/staudenmeir/eloquent-eager-limit – Jonas Staudenmeir Nov 20 '18 at 04:41

1 Answers1

10

You can't use limit/skip when eager loading, for it will limit whole related result.

I suppose you use MySQL, so here is what you need: http://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/

It's way to long to paste here, so just to get the idea: You need MySQL variables to do the job of fetching n per parent for you, like:

public function latestTwoComments()
{
  return $this->hasMany('Comment', 'comment_parent_id')->latest()->nPerGroup('comment_parent_id', 2);
}

//then
$comments = Comment::with('latestTwoComments')->get();
// now all the comments will have at most 2 related child-comments

Note: it's meant for hasMany relation and MySQL

Jarek Tkaczyk
  • 78,987
  • 25
  • 159
  • 157
  • Thanks, I was thinking a solution for this and you came up with one, +1 my friend :-) – The Alpha Oct 08 '14 at 08:07
  • Hmmm.. I tried having the latestTwoComments() relationship in the Comment model, which extends BaseModel, with all the mySql in BaseModel, but not working for me, the query can't find things. But when I follow your site example, and put the code latestTwoComments() and the hasMany in the Post model, it executes the query correctly. So it can't work in the comments model itself? Thanks a lot for your help! – Wonka Oct 08 '14 at 12:38
  • I noticed that it's actually searching in the posts_id IN(?, ?, etc..) when it's in the post model, so I can't seem to get it to work just right in the comments model itself. – Wonka Oct 08 '14 at 13:03
  • You must have done something wrong. It works just the same for self-referencing table. Show your relation for child-comments. – Jarek Tkaczyk Oct 08 '14 at 13:56
  • I updated the question to show the model, did I do something wrong or miss something? – Wonka Oct 08 '14 at 14:24
  • I suppose there's 2nd param missing in that relation: `hasMany('Comment', 'comment_parent_id')`, so obviously it won't work, since Eloquent will search for `comment_id` foreign key. – Jarek Tkaczyk Oct 08 '14 at 14:26
  • I updated the model, but the query now loads all, not limiting to latest two comments. I am also not sure how it will know to only get certain comments in the $CommentsIDs which is a list of latest 5 comments. – Wonka Oct 08 '14 at 14:37
  • And how you execute the query? The way I showed works with eager loading, you tried to load the comments without using relation. Show whole code, otherwise I cannot help you. – Jarek Tkaczyk Oct 08 '14 at 14:40