Selecting more than one table with Laravel Eloquent

1
votes

I have 3 tables - users, roles, and role_user. Each user is assigned to a role in the role_user table and the list of roles is found on the roles table. I want to select all users with role id of 12. I am currently doing this with the query builder but I need it to be in eloquent format. I am not really good with Laravel eloquent, please I need help in convert the queries to eloquent. This is what I am currently doing:

 $users = DB::table('users')
            ->join('role_user', 'users.id', '=', 'role_user.user_id')
            ->join('roles', 'roles.id', '=', 'role_user.role_id')
            ->where('roles.id', '=', 12)
            ->get();
4
phplaravellaravel-5eloquent

4 Answers

1
votes

To run your same DB query in an eloquent format, you would have to do the following:

  1. You should have a User model created which should be created with your project if you enabled laravel authentication which you could read more on here. If not you have to create your Models.

  2. To use the Models in your controller or Class, you have to enable it in your php file:

    use App\User;

  3. Run your reference to the User model like so.

    $users = User::join('role_user','users.id', '=', 'role_user.user_id') ->join('roles', 'roles.id', '=', 'role_user.role_id') -> where('roles.id', '=', 12) ->get();

$users will now be a collection and subject to all the great methods available with a collection.

I suggest taking some time to run through a free video tutorial on laracast to really understand how Model's are used with Eloquent. Additionally would be good to review the out of the box Auth features available. I tend to make all my calls to roles using Auth. Hope this helps.

2
votes

With Eloquent its very easy to retrieve relational data checkout following example with your scenario in Laravel 5

we have three models

1) Users ( has one role_user )

2) Roles (has many role_user)

3) RoleUser (belongs to User and Role )

1) Users.php

<?php

namespace App\Models;
 use Eloquent;

class Users extends Eloquent{

    protected $table = 'users';

    public function userrole()
    {
        return $this->hasOne('App\Models\RoleUser');
    }

}

2) Roles.php

<?php

namespace App\Models;

use Eloquent;

class Roles extends Eloquent
{
    protected $table = "roles";

    public function rolesuser()
    {
        return $this->hasMany('App\Models\RoleUser');
    }

}

2) RoleUser.php

<?php

namespace App\Models;
 use Eloquent;

class RoleUser extends Eloquent{

    protected $table = 'role_user';

    public function user()
    {
        return $this->belongsTo('App\Models\Users');
    }

    public function role()
    {
        return $this->belongsTo('App\Models\Roles');
    }

}

You need to check you database has relation and setup in models. Users has one role in role_user table. Role has many roles for users in role_user table. Role User belong to users and role. Once you setup the relationships in laravel/database, it becomes easy to retrieve the related information.

For example, if you want to retrieve an users by using the userrole, you would need to write:

$users = \App\Models\Users::with(['userrole'])->first();

If you want to get user role name than you can write:

$users->userrole->role->rolename; 
/* here rolename the column name you have in role table for role name*/

Now come to your query: You want to select those users only who has role id 12. It's now becomes simple to write in eloquent format.

$users = \App\Models\RoleUser::where('role_id', 12)->with(['user', 'role'])->get(); 
/* Here role_id is the column you have in role_user table as reference for role table */

you can learn more here: https://laravel.com/docs/5.6/eloquent-relationships

0
votes

This will give the uses with role id of 12:

User::whereHas( 'roles', function ( $query ) {
            $query->where( 'id', 12 );
        } )->get();

I assumed u set up the relationships correctly.

If u want dynamically pass the $role_id, then use the following:

User::whereHas( 'roles', function ( $query ) use ($role_id) {
            $query->where( 'id', $role_id );
        } )->get();
0
votes

In role Model you should add this 

public function users()
{
    return $this->hasMany('App\Models\User','role_id','id');
}

In Controller

$allusrs = Role::with('users')->where('id','=','12');
dd($allusrs);