Apr 5, 2021

Level 2

Get single role from user

how do I take the role of a user who has more than one role

example

I have two users with roles like this

John has a role => student

and

Doe has a role => student and teacher

How do I get user data with the roles teacher, while there is one user who has more than one role

chaudigv

5 years ago

Level 16

Please share your relevant models relationships.

Fajar

5 years ago

Level 2

@chaudigv I refer to easy roles and permission packages

rovshena

5 years ago

Level 2

Table structure

users
    id - integer
    name - string

roles
    id - integer
    name - string

role_user
    user_id - integer
    role_id - integer

Model structure

User model:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    /**
     * The roles that belong to the user.
     */
    public function roles()
    {
        return $this->belongsToMany(Role::class);
    }
}

Role model:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Role extends Model
{
    /**
     * The users that belong to the role.
     */
    public function users()
    {
        return $this->belongsToMany(User::class);
    }
}

Retrieve users which has role teacher

use Illuminate\Database\Eloquent\Builder;

// Retrieve users which has role teacher
$users = User::whereHas('roles', function (Builder $query) {
    $query->where('name', 'teacher');
})->get();

foreach ($users as $user) {
    foreach($user->roles as $role) {
		//
	}
}

Inverse of the relationship

$teacherRole = Role::firstWhere('name', 'teacher');

foreach($teacherRole->users as $user) {
	foreach($user->roles as $role) {
		//
	}
}

Fajar

5 years ago

Level 2

i get this

Column not found: 1054 Unknown column 'roles.user_id' in 'where clause' (SQL: select * from `roles` where `roles`.`user_id` = 1 and `roles`.`user_id` is not null) (View: /Users/fajardev/Sites/projects/smait/resources/views/layouts/app.blade.php) (View: /Users/fajardev/Sites/projects/smait/resources/views/layouts/app.blade.php)

rovshena

5 years ago

Level 2

I have expanded my reply.

esorone

5 years ago

Level 4

Hello,

Could you share your migration files as well? So we can see if there is an actual column? Because in your database structure I see: roles.id and role_user.user_id. So I don't see roles.user_id. Just ot make sure if the naming convention is being followed.

Fajar

5 years ago

Level 2

@esorone


 public function up()
    {
        $tableNames = config('permission.table_names');
        $columnNames = config('permission.column_names');

        Schema::create($tableNames['permissions'], function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('guard_name');
            $table->timestamps();
        });

        Schema::create($tableNames['roles'], function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('guard_name');
            $table->timestamps();
        });

        Schema::create($tableNames['model_has_permissions'], function (Blueprint $table) use ($tableNames, $columnNames) {
            $table->unsignedInteger('permission_id');

            $table->string('model_type');
            $table->unsignedBigInteger($columnNames['model_morph_key']);
            $table->index([$columnNames['model_morph_key'], 'model_type', ], 'model_has_permissions_model_id_model_type_index');

            $table->foreign('permission_id')
                ->references('id')
                ->on($tableNames['permissions'])
                ->onDelete('cascade');

            $table->primary(['permission_id', $columnNames['model_morph_key'], 'model_type'],
                    'model_has_permissions_permission_model_type_primary');
        });

        Schema::create($tableNames['model_has_roles'], function (Blueprint $table) use ($tableNames, $columnNames) {
            $table->unsignedInteger('role_id');

            $table->string('model_type');
            $table->unsignedBigInteger($columnNames['model_morph_key']);
            $table->index([$columnNames['model_morph_key'], 'model_type', ], 'model_has_roles_model_id_model_type_index');

            $table->foreign('role_id')
                ->references('id')
                ->on($tableNames['roles'])
                ->onDelete('cascade');

            $table->primary(['role_id', $columnNames['model_morph_key'], 'model_type'],
                    'model_has_roles_role_model_type_primary');
        });

        Schema::create($tableNames['role_has_permissions'], function (Blueprint $table) use ($tableNames) {
            $table->unsignedInteger('permission_id');
            $table->unsignedInteger('role_id');

            $table->foreign('permission_id')
                ->references('id')
                ->on($tableNames['permissions'])
                ->onDelete('cascade');

            $table->foreign('role_id')
                ->references('id')
                ->on($tableNames['roles'])
                ->onDelete('cascade');

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

        app('cache')
            ->store(config('permission.cache.store') != 'default' ? config('permission.cache.store') : null)
            ->forget(config('permission.cache.key'));
    }

esorone

5 years ago

Level 4

Goodmorning..

Im not into morph.. but what I did notice is that I don't see a roles.user_id in your migration file. The columns are: id, name, guard_name and no user_id

And basically this is what the error tells. So I would start to add the user_id to the roles table.