Laravel HasMany WhereNotIn Query

Problem: I’m trying to query a PeopleType to find all the courses where a person isn’t associated.

I have 4 tables

1. People
2. PeopleTypes
3. Courses
4. People_Courses
5. PeopleType_Courses

I have the following relationships

PERSON MODEL

public function getPeopleType() {
  return $this->belongsTo('AppPeopleType','type_id');
}

public function getCourses() {
  return $this->belpngsToMany('AppCourse','People_Courses','person_id','course_id');
}

PEOPLE_TYPE MODEL

public function getPeople() {
  return $this->hasMany('AppPerson','type_id');
}

public function getCourses() {
  return $this->belpngsToMany('AppCourse','PeopleType_Courses','people_type_id','course_id');
}

My attempt:

$peopleType = AppPeopleType::FindOrFail(1);
$courses = $peopleType->getCourses()->whereNotIn('id', function($q) use($person) {
                $q->select('course_id')
                  ->from('People_Courses')
                    ->where('person_id', $person->id);
           })->get();

My response:

Integrity constraint violation: 1052 Column ‘id’ in IN/ALL/ANY subquery is ambiguous

People Courses Table Schematic

Schema::create('People_Courses', function (Blueprint $table) {
   $table->increments('id');
   $table->integer('course_id');
   $table->integer('person_id');
);

PeopleType_Courses Table Schematic

Schema::create('PeopleType_Courses', function (Blueprint $table) {
   $table->increments('id');
   $table->integer('course_id');
   $table->integer('people_type_id');
);

Answer

When you’re working on relations that have similar column name being selected in the query you need to resolve the ambiguity by specifying the table name with the column. e.g:

$peopleType->getCourses()->whereNotIn('courses.id', function($q)...  //courses.id