Probleme querying roles with db query

Hi all,

I have that must run the DB query and not eloquent as it is a complicated query. The problem is that in my query I also need to pull the user spattie role

The user table -id

The "model_has_role" table -role_id -model_type -model_id

roles table: -id -name <----I need to get to this one from the DB query when querying the table user. -guard_name

How to do this without eloquent please?

Thanks.

MichalOravec

5 years ago

Best Answer

Level 75

You need use join for that

use Illuminate\Support\Facades\DB;

$users = DB::table('users')
    ->selectRaw('users.*, roles.name as role_name')
    ->join('model_has_roles', function ($join) {
        $join->on('users.id', '=', 'model_has_roles.model_id')
            ->where('model_has_roles.model_type', \App\Models\User::class);
    })
    ->join('roles', 'model_has_roles.role_id', '=', 'roles.id')
    ->get();

Docs: https://laravel.com/docs/8.x/queries#joins

User1980

5 years ago

Level 4

Thank you so much!

Please or to participate in this conversation.