CODEWITHSUNDEEP
phplaraveljoinlaravel-4eloquent
user0129e021939232
user0129e021939232

Reputation: 6355

Laravel 4 Eloquent table join and where clause on multiple tables

Hi I'm using Laravel and Eloquent in order to store users, there profile and there assigned roles. My table structure is as follows;

users

id | username | email | password | created_at | updated_at

profiles

id | user_id | firstname | created_at | updated_at 

roles 

id | name 

assigned_roles 

id | user_id | role_id

I'm trying to select all users where there role name is equal to admin. But when I do the following I get an empty array:

 return \User::with('profile')->join('assigned_roles', function ($join) {
        $join->where('assigned_roles.user_id', '=', 'users.id');
    })->get();

Any ideas what I'm doing wrong?

Also I am using Zizaco Entrust and Confide https://github.com/Zizaco/entrust/tree/1.0

Upvotes: 1

Views: 1194

Answers (1)

jedrzej.kurylo
jedrzej.kurylo

Reputation: 40909

In order to fetch users that have admin role you need to use Eloquent's whereHas() method:

$admins = \User::with('profile')
  ->whereHas('assigned_roles', function($query) {
    $query->whereName('admin');
  })
  ->get();

I assume you have assigned_roles relation defined. If not, add many-to-many relation between your users and roles:

public function assigned_roles() {
  return $this->belongsToMany('\Your\Model\Namespace\Role', 'assigned_roles', 'user_id', 'role_id');
}

Upvotes: 1

Related Questions