Laravel Eloquent Query within Foreach Loop

Question

Back again with another question I am hoping someone might have an idea for regarding the app that I am working on.

In this educational assessment application, I have assessments which have a one-to-many relationship with Competencies (similar to a Blog Post Category) and a many-to-many relationship with Contexts (similar to a Blog Tag).

I am trying to create a report view which will allow me to show a card for each of the Competency & Context combinations and then count all assessments where that competency & context combination exists (think of this as counting how many blog posts are in each category & tag combination, even if that number is 0).

So far I am able to produce the report which lists a card for each competency and context combination but I can't figure out how to pass that information to the controller for use in the query which will find the relevant assessments.

Here is my Report View

<x-app-layout title="{{ config('app.name', 'Laravel') }}">
  <div class="container grid px-6 mx-auto">
    <h2 class="my-6 text-2xl font-semibold text-gray-700 dark:text-gray-200">
      {{ __('Reports') }}
    </h2>
    <div class="grid gap-6 mb-8 md:grid-cols-2 xl:grid-cols-4">
      @foreach ($competencies as $competency) <br>
        @foreach ($contexts as $context)
          <div class="flex items-center p-4 bg-white rounded-lg shadow-xs dark:bg-gray-800">
            <div>
              <p class="text-lg font-semibold text-gray-700 dark:text-gray-200">
                {{ $competency->name}}
              </p>
              <p class="mb-2 text-sm font-medium text-gray-600 dark:text-gray-400">
                {{ $context->name }}
              </p>
              {{  $assessments }}
            </div>
          </div>
        @endforeach
      @endforeach
    </div>
</x-app-layout>

Here is my Report Controller

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Assessment;
use App\Models\Competency;
use App\Models\Context;
use App\Models\User;

use Illuminate\Support\Facades\Auth;


class ReportController extends Controller
{
    public function index(Competency $competency, Context $context)
    {
        return view('dashboard.reports.index', [
            'competencies' => Competency::where('team_id', Auth::user()->currentTeam->id)->get(),
            'contexts' => Context::where('team_id', Auth::user()->currentTeam->id)->get(),
            'assessments' => Assessment::where('competency_id', $competency->id)->whereRelation('contexts', 'context_id', $context->id)->count(),
        ]);
    }
}

Here is an image of what this currently produced in the Report View: https://i.sstatic.net/70rxg.jpg

Please let me know if there is additional detail which would be helpful

Answer 1

it's better you declare the relation in the model, let's assume:

1. an assessment has one category,
2. an assessment has many contexts,

You can create an additional table with its model to store assessment contexts (because it assessent can has many contexts), let's say the table name is assessment_contexts and the model name is AssesmentContext. Or you can just run php artisan make:model AssessmentContext -m. At least it has 2 columns, assessment_id and context_id,

Then inside AssessmentContext, add this function to create a simple relation,

public function assessment() {
   return $this->hasOne(Assessment::class);
}

public function context() {
   return $this->hasOne(Context::class);
}

Declare this functions inside the Assessment model,

public function competency() {
   return $this->hasOne(Competency::class);
}

public function contexts() {
   return $this->hasMany(AssesmentContext::class);
}

And add the this to the Competency model,

public function assessments() {
   return $this->hasMany(Assessment::class);
}

And this for the Context model,

public function assessments() {
   return $this->hasMany(AssessmentContext::class);
}

You have to add foreign key inside assessments table that refers to category primary key id, (I recommend you the column name is category_id).

Then finally in your controller, you can just declare the competencies list, and inside your view, you can access all the relate data. i.e:

return view('dashboard.reports.index', [
   'competencies' => Competency::where('team_id', Auth::user()->currentTeam->id)->get()
]);

Inside view,

@foreach ($competencies as $competency) <br>
   @foreach ($competency->assessments as $assessment)
      <div class="flex items-center p-4 bg-white rounded-lg shadow-xs dark:bg-gray-800">
         <div>
            <p class="text-lg font-semibold text-gray-700 dark:text-gray-200">
               {{ $competency->name}}
            </p>
            @foreach ($assessment->contexts as $context)
               <p class="mb-2 text-sm font-medium text-gray-600 dark:text-gray-400">
                  {{ $context->name }}
               </p>
            @endforeach
         </div>
      </div>
   @endforeach
@endforeach

This is just simple scheme of relation, there are other ways to do it that maybe better and more optimal.