Error Many to Many Relationship in Laravel 5.8

I got an error when I try to retrieve many to many relationship data in Laravel. The message of error is

SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘id’ in where clause is ambiguous (SQL: select * from jobs where (not exists (select * from job_types inner join job_types_jobs on job_types.id = job_types_jobs.job_types_id where jobs.id = job_types_jobs.jobs_id and job_types.deleted_at is null) or exists (select * from job_types inner join job_types_jobs on job_types.id = job_types_jobs.job_types_id where jobs.id = job_types_jobs.jobs_id and id = 3 and job_types.deleted_at is null) and name LIKE %%) and jobs.deleted_at is null)

Table
enter image description here

JobTypes.php

public function jobs() // children  
{
    // one type of job has many jobs
    return $this->belongsToMany('AppJobs'); // id refer to jobs.id
}

Jobs.php

public function job_types() // parent 
{
    // one job only belongs to one type of job
    return $this->belongsToMany('AppjobTypes');
}

JobTypeController.php

public function assignJob($id)
{
    $jobType = AppJobTypes::withTrashed()->find($id);
    $jobs = $jobType->jobs;

    return view('job-types.assign-job', compact(['jobType', 'jobs']));
}

View

<label for="name">Job</label>
                <select selected="selected" multiple class="form-control {{ $errors->first('jobs') ? "is-invalid" : "" }}" name="jobs[]" id="jobs" class="jobs"></select>
                <div class="invalid-feedback">
                    {{ $errors->first('jobs') }}
                </div>

Migarations

Schema::create('job_types', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->timestamps();
    });

    Schema::create('jobs', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->timestamps();
    });

    Schema::create('jobs_job_types', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('jobs_id')->unsigned()->index();
        $table->integer('job_types_id')->unsigned()->index();

        $table->foreign('jobs_id')->references('id')->on('jobs')->onDelete('cascade');
        $table->foreign('job_types_id')->references('id')->on('job_types')->onDelete('cascade');
    });

Source: Laravel

Leave a Reply