Laravel Many-to-Many Relationship Field Names

Question

I have a users table and a permissions table. One user can have many permissions, and one permission can have many users:

USER ID | PERMISSION ID
1 | 1
1 | 2
2 | 1
2 | 1

There is a linking table called permission_user as defined by the Laravel spec for auto-inferring these tables.

If I define the following functions:

User Model:

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

Permission Model:

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

I get an error when calling App\User::first()->Permissions()->attach(App\Permission::first()); that says

 Illuminate\Database\QueryException  : SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'field list' (SQL: insert into `permission_user` (``, `user_id`) values (3, 1))

The database migration file:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
use Carbon\Carbon;
use App\User;
use App\Permission;
use App\Http\Resources\User as UserResource;
class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('permissions', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->timestamps();
        });

        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('username')->unique();
            $table->string('name')->unique();
            $table->string('email')->unique();
            $table->boolean('verified');
            $table->timestamps();
        });


        Schema::create('permission_user', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('permission_id');
            $table->unsignedBigInteger('user_id');
            $table->timestamps();
            $table->foreign('permission_id')->references('id')->on('permissions');
            $table->foreign('user_id')->references('id')->on('users');
        });

        $this->add_permission('View Projects');
        $this->add_permission('Edit Projects');
        $this->add_permission('View Users');
        $this->add_permission('Edit Users');

        $user = new User();
        $user->name = 'John Smith';
        $user->email = 'john@smith.com';
        $user->username = 'jsmith';
        $user->verified = 1;
        $user->save();


        $user->permissions()->attach(Permission::where('name','View Users')->first()->id); // -> This line it can't tell that permission_user.permission_id is where the permission.id field goes;

        $perms  = $user->permissions()->get();

        foreach($perms as $perm)
        {
            echo $perm->name . '\n';
        }

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement('SET FOREIGN_KEY_CHECKS = 0');
        Schema::dropIfExists('permission_user');
        Schema::dropIfExists('project_user');
        Schema::dropIfExists('permissions');
        Schema::dropIfExists('deployment_log');
        Schema::dropIfExists('branches');
        Schema::dropIfExists('projects');
        Schema::dropIfExists('users');
        DB::statement('SET FOREIGN_KEY_CHECKS = 1');
    }

    private function add_permission($permission_name)
    {
        DB::table('permissions')->insert(
            array(
                'name' => $permission_name,
            )
        );
    }
}

It appears Laravel(5.8) is unable to disscern from the linking table that permission_id is the field for the foreign reference to the permission.id field even though the database migration reflects that user_id is a foreign reference to user.id and permission_id is a foreign reference to permission.id.

I can solve this by specifying the linking table name, field name, and foreign key name in the belongsToMany function, however, Laravel's own documentation states that this isn't needed when tables and fields are named appropriately, which mine are. Is this a bug in Laravel? Do I need to change the name of the permission_user.permission_id field? How do I solve this without having to specify these names in my models as it's time consuming and not needed according to Laravel(5.8)'s documentation.

Answer 1

According to laravel docs:

[...] Many users may have the role of "Admin". To define this relationship, three database tables are needed: users, roles, and role_user. The role_user table is derived from the alphabetical order of the related model names, and contains the user_id and role_id columns.

The linking table must contain only the foreign keys from each model. Otherwise, you need to specify which relationship table you are using and the primary key for each model of the relation, as specified on laravel documentation.

As i said in the comments section, if you create your permission_user table with only permission_id and user_id columns and with this columns as primary keys, it will work as expected:

Schema::create('permission_user', function (Blueprint $table) {
    $table->unsignedBigInteger('permission_id');
    $table->unsignedBigInteger('user_id');
    $table->foreign('permission_id')->references('id')->on('permissions');
    $table->foreign('user_id')->references('id')->on('users');

    $table->primary(['permission_id', 'user_id']);
});

Here is a package that i have developed to handle user permissions and you can check the user_has_permissions table definition, which is, basically, a table that does exactly what your permission_user table does, by clicking this link.

Hope it helps.