Level 104
It would be helpful to the discussion if you shared the Eloquent query you have written
Sep 4, 2024
5
Level 4
Roles pivot table multiple queries
Hi I have checked the debug bar and there are a number of duplicate queries for
select exists(select * from `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` where `role_user`.`user_id` = 3 and `name` = 'admin') as `exists`
What can I do to mitigate this? I did some research but there are multiple way of stopping this from happening, on the user model or the App service provider, but I am a bit stuck on the best way to achieve this, thanks
Level 4
@tykus I found the issue I had:
public function isAdmin(): bool
{
return $this->roles()->where('name', 'admin')->exists();
}
I removed the function call and did this instead
public function isAdmin(): bool
{
return $this->roles->contains('name', 'admin');
}
Can I ask why adding roles() makes so many queries? Thanks
Level 104
@Cushty just be aware that fix requires the roles relation to be loaded otherwise you reintroduce the N+1 problem again. If you are already eager-loading the roles relation, then you are good. However, depending on your specific needs you can sometimes be more efficient using a JOIN rather than loading entire the Collection of Role instances on every User instance.
Level 50
@Cushty Calling $this->roles() will produce a query builder that you'll have to execute if you want results. When you call $this->roles for the first time, it'll execute the query and store the resulting roles in the model object. All subsequent $this->roles calls on the same object will use that collection instead of making new database queries.
Level 73
I'm guessing that you want to display some icon for users that has the admin role.
Then do an outer join directly in your main query that way you already have the information passed to your view.
Please or to participate in this conversation.