hasManyThrough Pivot

Trying to figure out how I can do a hasManyThrough relationship going through two pivot tables. I have Roles and Permissions and Users. There are many to many from Roles to Permissions and many to many from Users to Roles, but I want to be able to pull all valid permissions for a particular user.

The following raw statement will return what I am looking for.

SELECT
	`permissions`.*
FROM
	`permissions`
	INNER JOIN `permission_role` ON `permissions`.`id` = `permission_role`.`permission_id`
WHERE
	`permission_role`.`role_id` in(
		SELECT
			`roles`.id FROM `roles`
			INNER JOIN `role_user` ON `roles`.`id` = `role_user`.`role_id`
		WHERE
			`role_user`.`user_id` = 1)
GROUP BY
	`permissions`.slug

MichalOravec

4 years ago

Best Answer

Level 75

Has Many Through relationship has nothing to do with Many To Many relationships.

$roleIds  = User::find(1)->roles()->pluck('id');

$permissions = Permission::whereHas('roles', function ($query) use ($roleIds) {
    $query->whereIn('id', $roleIds);
})->get();

1 like

jrmy

4 years ago

Level 4

I think I was overthinking this. I was thinking I needed to return a relationship instance, but the more I dig in I can't think of an exact reason I need it to be a relationship instance so you're solution is right on! Thank you @michaloravec

Please or to participate in this conversation.