How to setup relationship between 3 Model in Laravel?

Question

I know there is a lot of questions similar to this but I couldn't find a better answer. So let's get to the point.

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:

1. ONE academic year(2020) has many classrooms
2. Each classroom for that year(2020) can have more than ONE faculties
3. 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.

Answer 1

I was able to figure this out on my way. Maybe this is not the best approach but I least took me somewhere. If someone has a better approach or there is some issues with this approach please feel free to share it here.

I setup the relationship as follow:

//*********ACADEMICYEAR MODEL *************

public function classrooms()
{
  return $this->belongsToMany('App\Model\Setting\Classroom', 'academic_year_classroom);
}


//***********FACULTY MODEL ********************** //
public function classrooms()
{
  return $this->belongsToMany('App\Model\Setting\Classroom', 'academic_year_classroom');
}


//********CLASSROOM MODEL **************
public function academicyears()
{
  return $this->belongsToMany('App\Model\Setting\AcademicYear');
}

public function faculties()
{
  return $this->belongsToMany('App\Model\Setting\Faculty', 'academic_year_classroom', 'classroom_id', 'faculty_id');
}

//1. I was able to return the records for a single year with it classrooms and faculties assigned to that classroom as follow:

public function show($id)
{
  return $year = AcademicYear::with(['classrooms', 'classrooms.faculties' => function ($q) use($id) {
    $q->where('academic_year_id', $id);
  }])->findOrFail($id);
}

I was also able to return the list of all the years with classrooms for each year and the faculties that were assigned to each of the classrooms as follow:

   public function index()
  {
   // First I retrieve all the Year IDs as an array
    $yearID = AcademicYear::pluck('id')->toArray();

    foreach($yearID as $yr) {
      $year = AcademicYear::with(['classrooms', 'classrooms.faculties' => function ($q) use($yr) {
        $q->whereIn('academic_year_id', [$yr]);
      }])->orderBy('year', 'DESC')->get();
    }
      return $year;
  }

Again, if you have a better way to approach this, please share with us. And if this answer was helpful please vote it so that it can help others. Thanks.

Answer 2

I would suggest addiung foreign keys to classrooms and faculties tables.

Afterwards you need to assign add relations between each model, belongsToMany() and hasMany().

Once defined you can use with() and whereHas to fetch relevant data.

To fetch all classrooms and faculties you'd use:

AcademicYear::with('classroom.faculty')
  ->where('year', 2020);

I want to return Year:2020 with classrooms: Grad-1 with faculty-1, Grad-2 with faculty-1

AcademicYear::with('classroom.faculty')
  ->whereHas('classroom', function($query) {
    $query->whereIn('name', ['Grad-1', 'Grad-2']);
      ->whereHas('faculty', function($query) {
        $query->where('name', 'faculty-1');
      });
  })
  ->where('year', 2020);

You should also be able to do something like below (not sure how query would look for that):

AcademicYear::with([
    'classroom' => function($query) {
      $query->whereIn('name', ['Grad-1', 'Grad-2']);
    },
    'faculty' => function($query) {
      $query->where('name', 'faculty-1');
    })
  ])
  ->where('year', 2020);

reference - https://laravel.com/docs/7.x/eloquent-relationships#constraining-eager-loads

And... if you are to stick with the same structure you can define same relations, hasManyThrough, but via your pivot table.

---

Update:

One other thing that you can do, is add a Model for your Pivot table and defining relationships between pivot and your other models.

I want to return Year:2020 with classrooms: Grad-1 with faculty-1, Grad-2 with faculty-1

MyPivotModel::with([
    'academic_year', 
    'classroom', 
    'faculty'
  ])
  ->whereHas('classroom', function($query) {
    $query->whereIn('name', ['Grad-1', 'Grad-2']); 
  })
  ->whereHas('faculty', function($query) {
    $query->where('name', 'faculty-1');
  })
  ->whereHas('academic_year', function($query) {
    $query->where('year', 2020);
  });