I am a beginner to Laravel and I have the following schema I'm playing with:

Course {
  id,
  name,
  teacher_id
}

Category {
  id,
  name
}

Teacher {
  id,
  name
}

CategoryCourses {
  id,
  course_id,
  category_id
}

As you can see, CategoryCourses is my pivot table and I'm using belongsToMany between Courses & Category.

Now - what do I want to do? I'm trying to create a show view for Category where I click on the Category and it should display all the Courses with that category AND the Teacher whose teaching that respective Course.

So for example - if I have 4 Laravel courses that have the "Web Development" category - I want to display all of them on a card, with the Teacher's name on each of them.

Following are my models:

Category

class Category extends Model
{
    use HasFactory;

    protected $fillable = [
        'name'
    ];

    public function courses()
    {
        return $this->belongsToMany(Course::class, 'course_categories', 'course_id', 'category_id');
    }

    public function teacher()
    {
        return $this->hasOneThrough(Teacher::class, CourseCategory::class);
    }
    
}

Course:

    class Course extends Model
{
    use HasFactory;

    protected $fillable = [
        'name','description','cover','difficulty', 'teacher_id'
    ];

    public function teachers() {
        return $this->belongsTo(Teacher::class, 'teacher_id', 'id');
    }

    public function categories() {
        return $this->belongsToMany(Category::class, 'course_categories');
    }
}

Teacher:

class Teacher extends Authenticatable
{
    use HasFactory;

    protected $fillable = [
        'name','email','password', 'avatar'
    ];

   public function courses() {
       return $this->hasMany('Course', 'teacher_id');
   }

}

CategoryController - show($id):

public function show($id)
    {
        $category = Category::findOrFail($id)->first();

        
        $course = Category::with('courses','teacher')->get();
        return view('admin.showcoursesbycategory', compact('category', 'course'));
    }

This is the error I'm getting after writing the $course = Category::with('teacher') code:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'teachers.course_category_id' in 'on clause' (SQL: select `teachers`.*, `course_categories`.`category_id` as `laravel_through_key` from `teachers` inner join `course_categories` on `course_categories`.`id` = `teachers`.`course_category_id` where `course_categories`.`category_id` in (1, 2, 3, 4))

How do I achieve what I'm trying to do? What am I doing wrong? Kindly help me out here.

CodePudding user response：

Assuming that each course has only one teacher, following normal naming conventions the relationship should be named as teacher on the Course model

class Course extends Model
{
    use HasFactory;

    protected $fillable = [
        'name','description','cover','difficulty', 'teacher_id'
    ];

    public function teacher() {
        return $this->belongsTo(Teacher::class, 'teacher_id', 'id');
    }

    public function categories() {
        return $this->belongsToMany(Category::class, 'course_categories', 'course_id', 'category_id');
    }
}

I guess you need to fix the relationship signature in Category model

class Category extends Model
{
    use HasFactory;

    protected $fillable = [
        'name'
    ];

    public function courses()
    {
        /**
         * Change
         * return $this->belongsToMany(Course::class, 'course_categories', 'course_id', 'category_id');
         */
        return $this->belongsToMany(Course::class, 'course_categories', 'category_id', 'course_id');
    }

    public function teacher()
    {
        return $this->hasOneThrough(Teacher::class, CourseCategory::class);
    }
    
}

Then in the controller you can write the query as

public function show($id)
{
    $category = Category::with('courses.teacher')->findOrFail($id);


    return view('admin.showcoursesbycategory', compact('category'));
}

Then in the view you can

<h1>{{ $category->name }}</h1>

<p>======== Courses ========</p>

@foreach($category->courses as $course)
    <p>{{ $course->name }}</p>
    <p>Conducted By: {{ $course->teacher->name }}</p>
@endforeach