better query and memory usage when checking for permission

[alirzaj]

hello every one.
I was wondering if the query for checking permission of a user can be improved.

when we use hasDirectPermission method it excecutes a query like this:

select permissions.*, model_has_permissions.model_idaspivot_model_id, model_has_permissions.permission_idaspivot_permission_id, model_has_permissions.model_typeaspivot_model_typefrompermissionsinner joinmodel_has_permissionsonpermissions.id=model_has_permissions.permission_idwheremodel_has_permissions.model_id= 1 andmodel_has_permissions.model_type = 'App\Models\User'

the code that runs this query and checks if user has permission is this:

return $this->permissions->contains('id', $permission->id);

in other words we load all of the user's permissions and perform the check in memory via a collection method.

what if we ran an exists query for that?
the code would be like this:

$this->permissions()->where('id', $permission->id)->exists();

and the query will be like this:

select exists(select * from permissionsinner joinmodel_has_permissionsonpermissions.id=model_has_permissions.permission_idwheremodel_has_permissions.model_id= 1 andmodel_has_permissions.model_type= 'App\Models\User' andid= 10) asexists``

the benefit is that executed query will take less time because no data is being selected and only existence is checked and less models will be hydrated. for example if the user has 100 permissions in first case 100 model will be loaded from database but in second approach no model will be hydrated.

in my local enviornment 1000 permissions and a user that had 100 permissions first query took 2.16ms and second one will take 620us

if this is correct I'll be happy to make a pr for both permission and role

[erikn69]

Which version are you using?

[alirzaj]

latest version (main branch)

[erikn69]

But if somebody has many hasDirectPermission, it's going to call database everytime on $this->permissions()->where('id', $permission->id), a benchmark is necessary for these cases?

Also adding select('id','name','guard_name') for avoid extra fields on BelongsToMany could help

use more roles instead of direct permissions

[alirzaj]

yeah you are right. it depends on use case

[erikn69]

Can you test mi last pull request?
I'm trying to make everything faster
Make cache more than 90% smaller and Speed up permissions cache lookups,
If everybody helps maybe we can get better times and spend less resources, and your review can be helpful
Note: for test you have to rebuild cache

[alirzaj]

of course I'll look into it