Laravel many-to-many-to-many throught relationship

Hej guys,

currently I am developing a Laravel web app with a kind of hard to solve through-relationship. My relationship looks like this:

The problem is: I want to have a courses relationship on my module model. I tried to solve it with a custom has-many relationship that works ... kind of. Well, yes, it works, but only if I access it directly. If I tried to use this relationship with something like whereHas('courses') it fails and builds an incorrect query. I can work around this by using whereHas('module_numbers.courses'), but actually, that's not the way I want it to be – if there is a better solution, that I haven't found yet.

Any pro ideas on how to solve my problem?

My current "solution":

public function courses() {
  $query = (
    Course::query()
      ->select('courses.*')
      ->join(
        "course_module_number",
        "courses.id",
        "course_module_number.course_id"
      )
      ->join(
        "module_numbers",
        "course_module_number.module_number_id",
        "module_numbers.id"
      )
      ->join(
        "modules",
        "module_numbers.module_id",
        "modules.id"
      )
      ->distinct()
  );

  return $this->newHasMany(
    $query,
    $this,
    $this->getForeignKey(),
    $this->getKeyName()
  );
}

EDIT (2022-01-17): changed database schema:

Solution 1:

I would solve this as following

In your Course model

//import Module model

public function modules(){
  return $this->belongsToMany(Module::class);
}

In your Module model

//import Course model

public function courses(){
  return $this->belongsToMany(Course::class);
}

Now you can access using eagerloading as follows

$courses = Course::with('modules')->get();

$modules = Module::with('courses')->get();

OR

use it with has if you want get as with at least one data

$courses = Course::has('modules')->get();

$modules = Module::has('courses')->get();

Now you can use foreach or if you want to access relationship you can do following

 foreach($courses as $key => $value){
   $value->modules->credit_points
 }

OR for module

 foreach($modules as $key => $value){
   $value->courses->semester
 }