php - Is there a way to get a nested Eloquent model based on ids from another table?

Question

Hey there stackoverflow

I am currently building a course application as part of my laravel project.

My problem lies in how the eloquent handle model relations, i'm still kinda new to eloquent, so hopefully you can answer my question.

The structure

The Course has many episodes and each episode has many sections.

Which means I have 3 tables in the DB. Courses -> course_episodes -> course_episode_sections

ID table is where i connect courses with users - course_users.

Right now i can create courses and and put in all the data correctly.

The Problem

I need to retrieve all the courses and its nested children that the user has bought, which is connected in the course_users table with columns course_id and user_id

Course structure

Same stucture in DB

                course: {
                name: null,
                sub_title: null,
                estimate: null,
                trailer: null,
                type: null,
                text: null,

                course_episodes: [
                    {
                        name: null,
                        section: [
                            {
                                order: null,
                                type: null,
                                content: null,
                            },
                        ]
                    },
                ]

            }

Model Pictures

My models as of right now.

class CourseUsers extends Model {
protected $fillable = [
    'id',
    'course_id',
    'user_id',
    'active',
];

protected $hidden = [
    'deleted_at',
    'updated_at',
    'deleted_at'
];


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

public function user(){

    return $this->belongsTo(User::class);
}

public function scopeFindForUserId($query, $userId)
{
    return $query->where(function ($q) use ($userId) {
        $q->where(function ($q) use ($userId) {
            $q->where('user_id', $userId);
        });
    });
}

Course model

class Course extends Model{
protected $fillable = [
    'id',
    'name',
    'sub_title',
    'type',
    'estimate',
    'trailer',
    'gateway_id',
    'text',
    'active',
];


protected $hidden = [
    'deleted_at',
    'updated_at',
    'deleted_at'
];


public function courseEpisode()
{
    return $this->hasMany(CourseEpisode::class);
}

public function courseUsers() {

    return $this->hasMany(CourseUsers::class);
}

public function scopeActive(Builder $builder)
{
    return $builder->where('active', true);
}

Course episode Model

class CourseEpisode extends Model implements HasMedia {
use HasMediaTrait;

protected $fillable = [
    'id',
    'course_id',
    'order',
    'name',

];

protected $hidden = [
    'deleted_at',
    'updated_at',
    'deleted_at'
];

public function course()
{
    return $this->belongsTo(Course::class);
}

public function courseSection()
{
    return $this->hasMany(CourseEpisodeSection::class);
}

Course episode sections

class CourseEpisodeSection extends Model {

protected $fillable = [
    'id',
    'course_episode_id',
    'order',
    'type',
    'content'

];

protected $hidden = [
    'deleted_at',
    'updated_at',
    'deleted_at'
];

public function courseEpisode()
{
    return $this->belongsTo(CourseEpisode::class);
}

Answer 1

According to your explanation, course_users table holds many-to-many relationship between Course and User model. In case of a many-to-many relationship, you actually don't need a CourseUser model. This kind of table which holds many-to-many relationship is called pivot table. Read more from the Official Documentation

I am defining only the relationships with your Course, User, CourseEpisode, CourseEpisodeSection models.

Course.php

class Course extends Model
{
  public function courseEpisodes()
  {
    return $this->hasMany(CourseEpisode::class);
  }

  public function users()
  {
    return $this->belongsToMany(User::class,'course_users')->withPivot('active');
  }
}

CourseEpisode.php

class CourseEpisode extends Model
{
  public function courseSections()
  {
    return $this->hasMany(CourseSection::class);
  }
}

User.php

class User
{
  public function courses()
  {
    return $this->belongsToMany(Course::class,'course_users')->withPivot('active');
  }
}

If you want to get all the children relationships from a user, use nested eager loading :

$user_with_nested_course_data = User::with('courses.courseEpisodes.courseSections)->find($id);