Laravel-permission: Unknown column 'model_has_roles.user_role_id'

[MadMikeyB]

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'model_has_roles.user_role_id' in 'field list' (SQL: select `user_roles`.*, `model_has_roles`.`model_id` as `pivot_model_id`, `model_has_roles`.`user_role_id` as `pivot_user_role_id` from `user_roles` inner join `model_has_roles` on `user_roles`.`id` = `model_has_roles`.`user_role_id` where `model_has_roles`.`model_id` = 1 and `model_has_roles`.`model_type` = App\User)

My code which is triggering this is as follows:

    /**
     * Is the current user an administrator.
     *
     * @return bool
     */
    public function isAdmin()
    {
        if ($this->hasRole('Super Administrator|Files Administrator|Forum Administrator')) {
            return true;
        }
        return false;
    }

I cannot see why or where user_role_id is being created in a migration or referenced in the code.

Is 2.7.7 a release which causes a breaking change? I couldn't find any upgrade notes. Reverting to 2.7.6 resolves this issue.

Thanks!

[drbyte]

have you customized your table names in the config file? Or is the config file untouched?

Is it possible that you've configured your Roles tablename to user_roles instead of roles?
I'm wondering if #577 creates a breaking change for you?

[MadMikeyB]

<?php

return [

    'models' => [

        /*
         * When using the "HasRoles" trait from this package, we need to know which
         * Eloquent model should be used to retrieve your permissions. Of course, it
         * is often just the "Permission" model but you may use whatever you like.
         *
         * The model you want to use as a Permission model needs to implement the
         * `Spatie\Permission\Contracts\Permission` contract.
         */

        'permission' => Spatie\Permission\Models\Permission::class,

        /*
         * When using the "HasRoles" trait from this package, we need to know which
         * Eloquent model should be used to retrieve your roles. Of course, it
         * is often just the "Role" model but you may use whatever you like.
         *
         * The model you want to use as a Role model needs to implement the
         * `Spatie\Permission\Contracts\Role` contract.
         */

        'role' => Spatie\Permission\Models\Role::class,

    ],

    'table_names' => [

        /*
         * When using the "HasRoles" trait from this package, we need to know which
         * table should be used to retrieve your roles. We have chosen a basic
         * default value but you may easily change it to any table you like.
         */

        'roles' => 'user_roles',

        /*
         * When using the "HasRoles" trait from this package, we need to know which
         * table should be used to retrieve your permissions. We have chosen a basic
         * default value but you may easily change it to any table you like.
         */

        'permissions' => 'user_permissions',

        /*
         * When using the "HasRoles" trait from this package, we need to know which
         * table should be used to retrieve your models permissions. We have chosen a
         * basic default value but you may easily change it to any table you like.
         */

        'model_has_permissions' => 'model_has_permissions',

        /*
         * When using the "HasRoles" trait from this package, we need to know which
         * table should be used to retrieve your models roles. We have chosen a
         * basic default value but you may easily change it to any table you like.
         */

        'model_has_roles' => 'model_has_roles',

        /*
         * When using the "HasRoles" trait from this package, we need to know which
         * table should be used to retrieve your roles permissions. We have chosen a
         * basic default value but you may easily change it to any table you like.
         */

        'role_has_permissions' => 'role_has_permissions',
    ],

    /*
     * By default all permissions will be cached for 24 hours unless a permission or
     * role is updated. Then the cache will be flushed immediately.
     */

    'cache_expiration_time' => 60 * 24,

    /*
     * By default we'll make an entry in the application log when the permissions
     * could not be loaded. Normally this only occurs while installing the packages.
     *
     * If for some reason you want to disable that logging, set this value to false.
     */

    'log_registration_exception' => true,
];

Is my config - looks like I have indeed changed my roles table to user_roles - though it was so long ago I cannot recall.

Thanks for the fast reply @drbyte

[drbyte]

Okay, so yes there appears to be a breaking change in such cases. 👎

You'd need to manually update your pivot table field names to match the changes in your config file.

[MadMikeyB]

@drbyte I assume reverting the breaking change isnt an option?

[bsmathiasmoser]

i have the same problem. also changed table names in config/permission.php.

my solution so far:
i updated foreign key field names in the tables model_has_permissions, model_has_roles and role_has_permissions to fit conventions.
e.g: Permissions Table: bs_user_permissions, so i changed foreign key field to bs_user_permission_id

but i still have a problem with some model relations:

• Permission::roles()
  i think the pivotKeyNames in the belongsToMany method are missing

 public function roles(): BelongsToMany
    {
        $permissionsForeignKeyName = str_singular(config('permission.table_names.permissions')).'_id';
        $rolesForeignKeyName = str_singular(config('permission.table_names.roles')).'_id';

        return $this->belongsToMany(
            config('permission.models.role'),
            config('permission.table_names.role_has_permissions'),
            $permissionsForeignKeyName,
            $rolesForeignKeyName
        );
    }

• Role::permissions()
  the same here

Can somebody confirm this Bug in the Model Relations?

[drbyte]

Working on backing out that breaking change. This way no db changes will be required.
Should be up within the hour.

[drbyte]

v2.7.8 has been released.
Best not to use 2.7.7.

If you were using 2.7.7 you'll need to change the db schema for your pivot tables.

[MadMikeyB]

Thanks @drbyte for the fast response! 👍

[bsmathiasmoser]

Thanks for the fast update!!

[robjbrain]

This still seems to be an issue for me (using 2.33.0)

When using a model called UserRole I constantly get the following error:

Column not found: 1054 Unknown column 'user_role_id' in 'where clause' (SQL: select permission_id from role_has_permissions where user_role_id = 2)

This happens even when using the default config (deleted my own config).

I also put back the config with the only thing changed being the models.role class name.

So presumably it's based on the class name rather than a config setting?

From what I can tell the permissions() method on the Role class needs to force the role_id:

BEFORE

public function permissions(): BelongsToMany
    {
        return $this->belongsToMany(
            config('permission.models.permission'),
            config('permission.table_names.role_has_permissions')
        );
    }

AFTER

public function permissions(): BelongsToMany
    {
        return $this->belongsToMany(
            config('permission.models.permission'),
            config('permission.table_names.role_has_permissions'),
            'role_id'
        );
    }

[robjbrain]

Actually caching was throwing me, it seems like you also need it in the roles() method of the permissions class.

[drbyte]

Thanks for the detailed post.
I've released Version 2.34.0 to address this.