I have 3 tables with Model showing below:
//******* academic_years table**************
Schema::create('academic_years', function (Blueprint $table) {
$table->id();
$table->string('year')->unique();
});
//************classrooms table*************
Schema::create('classrooms', function (Blueprint $table) {
$table->id();
$table->string('name')->unique();
$table->string('slug');
});
//**********faculties table***************
Schema::create('faculties', function (Blueprint $table) {
$table->id();
$table->string('name')->unique();
$table->string('slug');
});
I want to have a many to many relationships between these Model:
ONE academic year(2020) has many classrooms;
Each classroom for that year(2020) can have more than ONE faculties;
A faculty can belongs to many classrooms.
I would like to setup a relationship that will allows to return the list on all years with their classrooms and faculties that belong to each of the classroom.
For example: I want to return Year:2020 with classrooms: Grad-1 with faculty-1, Grad-2 with faculty-1
My pivot table looks like this. Or maybe someone can suggest a better approach.
Here is what I have done so far but It is returning all the years(OK) with the classrooms assigned to each year(OK), but it returns on each year all faculties let's say "Computer Science" assigned to let's say Classroom A from all the academic years that have Classroom A.
//Classroom Modal
public function academicyears()
{
return $this->belongsToMany('App\Model\Setting\AcademicYear');
}
public function faculties()
{
return $this->belongsToMany('App\Model\Setting\Faculty', 'academic_year_classroom_faculty');
}
//Faculty Model
public function classrooms()
{
return $this->belongsToMany('App\Model\Setting\AcademicYear', 'academic_year_classroom_faculty');
}
public function academicyears()
{
return $this->belongsToMany('App\Model\Setting\AcademicYear', 'academic_year_classroom_faculty');
}
// AcademicYear
public function classrooms()
{
return $this->belongsToMany('App\Model\Setting\Classroom', 'academic_year_classroom_faculty')->withPivot('sub_classroom','faculty_id');;
}
public function faculties()
{
return $this->belongsToMany('App\Model\Setting\Faculty', 'academic_year_classroom_faculty');
}
// In Academic Year controller
public function index()
{
$years = AcademicYear::with('classrooms', 'classrooms.faculties')->orderBy('year', 'DESC')->get(['id', 'year', 'active_year']);
// $classooms = Classroom::where('')
return view(index, compact(''years));
}
// In view
@foreach($years as $yr)
<li> {{ $yr->classrooms->name}}
@foreach($yr->classrooms as $faculties) <span> {{ $faculties->name }}</span> @endforeach
</li>
@endforeach