Laravel - 有效地限制每个子项 [英] Laravel - Limit each child item efficiently

问题描述

我有这个:

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

其中 $CommentsIDs 是一个包含 3 个父评论 ID (1,2,3) 的数组.

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

我正在尝试为每个 $commentsID 检索 2 个回复(如果存在).所以总共 6 个回复(每个评论 2 个)应该与查询一起返回，如果回复存在，仅此而已.但是，使用 take(2) 后，它将回复限制为 2 个，并且我们只收到 2 个评论之一的回复.如何设置为以最有效的方式为每个评论 ID 获得 2 个回复，以及如何使用正确的嵌套在视图中呈现它们?

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?

类似于:

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);
 }

}

查询:

$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)

推荐答案

急速加载时不能使用limit/skip，因为它会限制整个相关结果.

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

我想您使用 MySQL，所以这里是您所需要的:http://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/

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/

在这里粘贴很长时间，所以只是想了解一下:您需要 MySQL 变量来完成为您获取每个父级 n 的工作，例如:

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

注意:它用于 hasMany 关系和 MySQL

Note: it's meant for hasMany relation and MySQL

这篇关于Laravel - 有效地限制每个子项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！