Laravel 4 Eloquent table join and where clause on multiple tables

Question

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

@trincot no that is correct. i have a function in app/models/User.php defining the relationship — user0129e021939232
– user0129e021939232, Commented Dec 20, 2015 at 9:36
Just to make sure... you have roles defined in "assigned_roles" for your users? If there are no corresponding rows, then no results would be returned. — jedrzej.kurylo
– jedrzej.kurylo, Commented Dec 20, 2015 at 9:37
@jedrzej.kurylo I don't have a model for this I'm using github.com/Zizaco/entrust/tree/1.0 but it doesn't have functionality to do this — user0129e021939232
– user0129e021939232, Commented Dec 20, 2015 at 9:39

jedrzej.kurylo · Accepted Answer · 2015-12-20 09:59:45Z

1

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');
}

edited Dec 20, 2015 at 9:59

answered Dec 20, 2015 at 9:46

jedrzej.kurylo

41.1k10 gold badges108 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user0129e021939232 Over a year ago

I get this error Call to undefined method Illuminate\Database\Query\Builder::assigned_roles() I don't have an assigned_roles model. just a table called assigned_roles and the name of the role is saved in the roles table.

jedrzej.kurylo Over a year ago

have you defined that relation in your User model? You need to add many-to-many relation between roles and users

user0129e021939232 Over a year ago

no i haven't. the role name is also saved in the roles table. my db schema is posted in the question :)

jedrzej.kurylo Over a year ago

Then define the relation - otherwise Eloquent will have no idea how to link users and their roles. And DB structure is useless if models are not posted as they contain all that Eloquent knows about your models, not the database schema.

user0129e021939232 Over a year ago

how would I define the eloquent relationship of the schema I have above? sorry its just that I'm confused I've tried a few combos none work. basically a user can have many roles. but this is assigned through assigned_roles which has a user_id and role_id. I've already said user hasOne->('Profile'); any help would be welcomed!

|

Collectives™ on Stack Overflow

Laravel 4 Eloquent table join and where clause on multiple tables

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related