Column not found: 1054 Unknown column 'managers.id' in 'on clause' in Laravel 5 Many-to-Many relationship query

0
votes

I am new to laravel. I am working on this laravel 5 app and I got stuck here.

I have two eloquent models 

class Manager extends Eloquent{

    public function supervisors()
    {
        return $this->belongsToMany('App\Supervisor')->withTimestamps();
    }

}

class Supervisor extends Eloquent {

    public function managers()
    {
        return $this->belongsToMany('App\Manager');
    }
}

Manager and Supervisor are related by a many to many relationship. I have followed the laravel 5 documentation and created my tables as expected.

e.g Manager Table Structure

  • user_id PK ( FK from a User table (references id))
  • grade

Supervisor Table Structure

  • user_id PK ( FK from a User table (references id) )
  • excellence

Pivot Table Structure (manager_supervisor)

  • manager_id PK (FK from Manager Table (references user_id))
  • supervisor_id PK (FK from Supervisor Table (references user_id))

When I execute the following query, the result is not as expected

 $user = App\User::find(2);
 $user->supervisor->managers;

Sample Result:

Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'managers.id' in 'on clause' (SQL: select `managers`.*, `manager_supervisor`.`supervisor_id` as `pivot_supervisor_id`, `manager_supervisor`.`manager_id` as `pivot_manager_id` from `managers` inner join `manager_supervisor` on `managers`.`id` = `manager_supervisor`.`manager_id` where `manager_supervisor`.`supervisor_id` is null)'

I was expecting the 'on clause' to return something like 

managers`.`user_id` = `manager_supervisor`.`manager_id` where `manager_supervisor`.`supervisor_id` is 2

What are my missing here? What can really be wrong?

1
mysqllaravellaravel-5eloquentmany-to-many
This is a bit tricky, because I personally would not set my tables up this way. However, Laravel looks for primary keys to be called id so if you are using a primary key with a different name you will need to set the primary key inside your model: $primaryKey='user_id'; – craig_h
Thank you so much @craig_h. Adding the variable works. I am beginning to experience unnecessary complexity for organizing my tables as such. It is an inheritance with a table per class. How would you suggest I organize my tables? – Fokwa Best

1 Answers

1
votes

All I needed to do was set a primary variable in both models:

class Manager extends Eloquent{

    protected $primaryKey = 'user_id';

    public function supervisors()
    {
        return $this->belongsToMany('App\Supervisor')->withTimestamps();
    }

}

class Supervisor extends Eloquent {

    protected $primaryKey = 'user_id';

    public function managers()
    {
        return $this->belongsToMany('App\Manager');
    }
}