problem with Using pivot table with Eloquent and Laravel

Question

Here is my problem. I have a table called user and table called skills, also I have a pivot table that connects these two called EmployeeSkill. I am trying to fetch the skills that belong to the user but when i use tinker it returns Base table or view not found: 1146 Table 'pfe_sirh_db.skill_user' doesn't exist (SQL: select skills.*, skill_user.user_id as pivot_user_id, skill_user.skill_id as pivot_skill_id, skill_user.employee_id as pivot_employee_id from skills inner join skill_user on skills.id = skill_user.skill_id where skill_user.user_id = 1)' and am using swagger by the way it returns this "withTimestamps": false

    class User extends Authenticatable{

protected $table = "users";
   public function skills()
    {
        return $this->belongsToMany(Skill::class);
    }
}

and

   class Skill extends Model
    {
       public function User()
        {
            return $this->belongsToMany(User::class);
        }

}

and the pivot table

    class EmployeeSkill extends Model
{
    protected $table = "employee_skills";

    protected $fillable = [
        'employee_id', 'skill_id', 'note'
    ];


}

Piazzi · Answer 1 · 2020-03-30T18:22:20.637

0

Your pivot table should be named skill_user (singular) following the model name they belong to. And you don't need a EmployeeSkill model for this relation to work, you only need the table.

As said in the documentation:

to determine the table name of the relationship's joining table, Eloquent will join the two related model names in alphabetical order. However, you are free to override this convention. You may do so by passing a second argument to the belongsToMany method

And then you should exclude this line:

protected $table = "employee_skills";

change your table name to skill_user

And in your pivot table you should use user_id as a foreign key

You can retrieve all skills from a user by doing this:

$user = User::find(1); $user->skills();

This will give you all the skills from the user with the id 1

Be aware that you can use any table name and any foreign key name but if you do that you need to specify the names on the relations. So i recommend you doing what i 've told you, since is the standard way

edited Mar 30 '20 at 18:22

answered Mar 30 '20 at 16:14

Piazzi

2,490
3
11
25

$user->skills(); => Illuminate\Database\Eloquent\Relations\BelongsToMany {#3054 +withTimestamps: false, – Hamza Zaafrane Mar 30 '20 at 17:17
$user->skills; Illuminate/Database/QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'employee_skill.user_id' in 'field list' (SQL: select `skills`.*, `employee_skill`.`user_id` as `pivot_user_id`, `employee_skill`.`skill_id` as `pivot_skill_id` from `skills` inner join `employee_skill` on `skills`.`id` = `employee_skill`.`skill_id` where `employee_skill`.`user_id` = 1)' – Hamza Zaafrane Mar 30 '20 at 17:17
hey, a realize i've made some mistake. Take a look at my answer now – Piazzi Mar 30 '20 at 18:18

problem with Using pivot table with Eloquent and Laravel

1 Answers1