laravel 5.4嵌套foreach循环中的重复查询

Question

在我的Laravel5.4项目中，我试图从模型文章和状态之间的关系中获取一些数据。关系类型为OneToMany，其中Article具有一个状态，Status具有多个项目。

为了获取想要的数据，我迭代通过模型文章项目的集合，这些项目是eagerLoade连同模型/关系状态一起借助方法其中有（）。第一次迭代构建了一个正确的查询，但是在每次迭代中，它都会追加由方法生成的查询。

我如何解决这个问题（？）当给予-

文章范本：

class Article extends Model
{  
 public function status()
 {
    return $this->belongsTo(ArticleStatus::class,'articleStatus_id');
 }
}

状态模型：

class ArticleStatus extends Model
{
 public function article()
 {
    return $this->hasMany(Article::class);
 }
}

将变量通过控制器传递到视图：

class RedactionController extends Controller
{
 /**
 * Display a listing of the resource.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $articles = Auth::user()->articles();
    $statuses = array_values(ArticleStatus::all()->pluck('status')->toArray());     

    return view('redaction',[
            'articles' => $articles,
            'statuses' => $statuses,
        ]);
}

视图的一部分，我希望在其中遍历数据并显示与文章状态相对应的数据：

<div class="tab-content">
            @foreach($statuses as $count => $status)

                    <div class="card-block tab-pane @if($count==0) active @endif" id="{{$status}}">
                        <table class="table">
                            <thead>
                                <tr>
                                    <th>#</th>
                                    <th>Titulok</th>
                                    <th>Vytvorené</th>
                                    <th>Publikované</th>
                                    <th>Upravené</th>
                                    <th class="text-center">Action</th>
                                </tr>
                            </thead>
                            <tbody>

                            <p class="mt-5"><b>{{$status}}</b></p>

                            @php 
                                $result = $articles->with('status')->whereHas('status', function ($query) use ($status)
                         {                                                                        
                           $query->where('status','=', $status);
                         })->toSql();   

                                echo $result;           
                            @endphp 
                          </tbody>
                        </table>
                    </div>

            @endforeach
            </div>

回声$结果变量形式的结果：

第1次迭代：

select * from `articles` where `articles`.`user_id` = ? and `articles`.`user_id` is not null and exists (select * from `article_statuses` where `articles`.`articleStatus_id` = `article_statuses`.`id` and `status` = ?)

第二次迭代：

select * from `articles` where `articles`.`user_id` = ? and `articles`.`user_id` is not null and exists (select * from `article_statuses` where `articles`.`articleStatus_id` = `article_statuses`.`id` and `status` = ?) and exists (select * from `article_statuses` where `articles`.`articleStatus_id` = `article_statuses`.`id` and `status` = ?)

第三次迭代：

select * from `articles` where `articles`.`user_id` = ? and `articles`.`user_id` is not null and exists (select * from `article_statuses` where `articles`.`articleStatus_id` = `article_statuses`.`id` and `status` = ?) and exists (select * from `article_statuses` where `articles`.`articleStatus_id` = `article_statuses`.`id` and `status` = ?) and exists (select * from `article_statuses` where `articles`.`articleStatus_id` = `article_statuses`.`id` and `status` = ?)

因此，查询建立每个foreach迭代，并在前一个迭代的末尾追加一些查询。如果你能给我一些建议，我将不胜感激。

谢谢




Answer 1

您必须从Foreach中取出查询，并将其放入索引函数中，类似于

$results = $articles->with('status')->whereHas('status', function ($query) use ($statuses)
                     {                                                                        
                       $query->whereIn('status', $statuses);
                     })->get();

这将获得所有在$status中具有所有状态的文章，然后您可以为您的视图创建一个结构，例如

$final_results = [];
foreach($results as $article)
{
    if( ! isset($final_results[$article->status->status]))
        $final_results[$article->status->status] = [];

    $final_results[$article->status->status][] = $article;
}

这样，您就有了一个属性status为ArticleStatus的数组，作为其文章的键，将$final_results变量传递给您的视图。

那么在你看来

<div class="tab-content">
        @foreach($final_results as $status => $articles)


                <div class="card-block tab-pane @if($count==0) active @endif" id="{{$status}}">
                    <table class="table">
                        <thead>
                            <tr>
                                <th>#</th>
                                <th>Titulok</th>
                                <th>Vytvorené</th>
                                <th>Publikované</th>
                                <th>Upravené</th>
                                <th class="text-center">Action</th>
                            </tr>
                        </thead>
                        <tbody>

                        <p class="mt-5"><b>{{$status}}</b></p>
                         @foreach($articles as $article)
                       // Here your code to show Article properties
                         @endforeach
                      </tbody>
                    </table>
                </div>

        @endforeach
        </div>