Verifying user roles multiple times in the blade template, doing the same select from database

[mkuzmych]

I use check: @if(Auth::user()->hasRole(['administrator']))...@endif multiple time at the same template, i get 10 selects from database select roles.*, role_user.user_id as pivot_user_id, role_user.role_id as pivot_role_id from roles inner join role_user on roles.id = role_user.role_id where role_user.user_id = '1'. Can I do this select once time? P.S.: I check user roles in different place and cannot group them. Thanks!!

[dneykov]

I'm facing exact same problem.

[Zizaco]

That is a problem for sure. I will be looking into it

[anis-bedhiafi]

Hi @Zizaco

I am having the same problem too.

For a simple page I have

620 statements were executed, 617 of which were duplicated, 3 unique

It is killing the server

Duplicates are roles and permissions:

select roles.*, role_user.user_id as pivot_user_id, role_user.role_id as pivot_role_id, role_user.created_at as pivot_created_at, role_user.updated_at as pivot_updated_at from roles inner join role_user on roles.id = role_user.role_id where role_user.user_id = '2'

select permissions.*, permission_role.role_id as pivot_role_id, permission_role.permission_id as pivot_permission_id from permissions inner join permission_role on permissions.id = permission_role.permission_id where permission_role.role_id = '2'

I attached a screenshot from debugger.

[carlosadames]

@anis-bedhiafi you should take a look to your queries. Maybe you might have some duplicates. I was facing the same issue but after revise my code and optimized it, i got rid of some duplicate queries.

In addition to this, i also follow the steps described here and this solved my issue. You need to use cache in order to avoid queries to be performed on every page load. Take a look to the comment by @big-zein here .

Add the following to config/cache.php:

'ttl' => 30 //In minutes

Let me know if this solves your problem.

[carlosadames]

@uapixart, take a look to my previous comment and let me know if it helps.

[anis-bedhiafi]

@carlosadames

Thanks for the reply.

This is surely a problem from Entrust itself and not my queries.

Look here I am rendering an empty page with no queries to the database.

class UserController extends Controller { public function index() { return view('user.dashboard'); } }

15 statements were executed, 14 of which were duplicated, 1 unique

Screenshot

[jhemmmm]

@carlosadames It does not cache the queries too. Got the very exact problem.

[mkuzmych]

@carlosadames, Dont work, this problem with module Zizaco/entrust, with must get user with roles first and then make select from local and not from database. Thanks!

[anis-bedhiafi]

@Zizaco Any updates on this bug? Should we consider other alternatives?

Thank you.

[git-mahfuz]

I was facing the same problem and after setting up 'ttl' => 30 //In minutes in config/cache.php, redis as cache driver and then running php artisan config:cache helped me to get rid of this problem. Previously it was extra 140+ queries due to dynamic menu as per permission and was causing me almost 1.5 secs delay in per page load. Taking a look at EntrustUserTrait (vendor > zizaco > entrust >src > Entrust > Traits > EntrustUserTrait.php) could be helpful to know how it works behind the scene.

[dflow]

same issue on laravel 5.4

[eantz]

In my case, add 'ttl' => 60 in app/cache.php solves the problem.

And I just made a new PR #908 to add new config to be used in cachedRoles and cachedPermissions functions, so we don't need to add new config in app/cache.php manually

[dflow]

@eantz what is setting ttl doing and what will it have an effect on besides entrust?

[eantz]

@dflow I think this https://github.com/Zizaco/entrust/issues/871#issuecomment-348867290 already explained it.