Get spatie roles included with the query builder.

I'm trying to create a query build and also include the user roles in spatie but how can I do it? I tried different options from previous forums but none of them fits my needs.

DB::table('users') ->select('users.id', 'users.name', 'users.email', 'banned_users.ban_date', 'banned_users.ban_reason') ->join('banned_users', 'banned_users.user_id', '=', 'users.id') ->where(['banned_users.banned_by' => 1]) ->get();

The end result should be something like:

[ { "id": 1, "name": "Dr. Brannon Paucek I", "email": "emery80@example.org", "ban_date": "1984-05-08", "ban_reason": "Lorem ipsum dolor sit amet.", "role": "admin" }, { "id": 4, "name": "Dwight Paucek", "email": "[email protected]", "ban_date": "1983-06-13", "ban_reason": "Lorem ipsum dolor sit amet.", "role": "guest" } ]

MichalOravec

5 years ago

Best Answer

Level 75

@rcvioleta If you use default spatie table names, it could be like this

use App\User;

DB::table('users')->select('users.id', 'users.name', 'users.email', 'banned_users.ban_date', 'banned_users.ban_reason', 'roles.name as role')
    ->join('banned_users', 'banned_users.user_id', '=', 'users.id')
    ->join('model_has_roles', function ($join) {
        $join->on('users.id', '=', 'model_has_roles.model_id')
             ->where('model_has_roles.model_type', User::class);
    })
    ->join('roles', 'model_has_roles.role_id', '=', 'roles.id')
    ->where(['banned_users.banned_by' => 1])
    ->get();

4 likes

rcvioleta

5 years ago

Level 1

@michaloravec thank you so much that works!

Please or to participate in this conversation.