laravel 5.4 duplicates of queries within a nested foreach loop

Question

In my Laravel 5.4 project i'm trying to fetch some data from a relationship between models Article and Status. The relationship type is OneToMany where Article has one Status and Status has many Articles.

To fetch the wanted data i iterate through a collection of model Article items which is eagerLoaded together with the model/relationship Status with help of a method whereHas(). First iteration builds a correct query, but with each other iteration it appends the query generated by the method whereHas() [that's my guess].

How can i solve this problem (?) when given ->

Article Model:

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

Status Model:

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

Passing variables through Controller to a view:

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

Part of the View where i want to iterate through the data and display them corresponding to article's 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>

The outcome in form of echoed $result variable:

1st Iteration:

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` = ?)

2nd Iteration:

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` = ?)

3rd Iteration:

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` = ?)

So the query builds up each foreach iteration and appends some query at the end of the previous one. If you could give me some advice i'd be thankful.

thanks

Answer 1

You must take out the query from the foreach and put it in your index function, something like

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

This get all Articles which have all Status in $statuses, then you can make an structure for your view like

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

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

With this you have an array with property status of ArticleStatus as keys with their articles, pass the $final_results variable to your view.

Then in your view

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