syncPermissions performs too many queries

[MohammedElattar]

I'm trying to update a role with the new given permissions

that is my code to update

• it creates too many queries in insertion

• i fixed it by using upsert method , i created a model called RoleHasPermission and using upsert method
  and i delete all old permissions first using detach method $role->permissions()->detach()

• so i was asking is something wrong with my code using syncPermissions or what ?

Thanks for everyone ❤

[parallels999]

so i was asking is something wrong with my code using syncPermissions or what ?

yes, it seems to be a something wrong with your code
try on a laravel fresh install

[MohammedElattar]

Yes I think like @erikn69 said it's expected behavior of attach method , but i made it in a single query and clear the cache after that would be better in performance. thanks ❤

[erikn69]

https://laracasts.com/discuss/channels/eloquent/eloquent-sync-method-does-separate-insertupdate-query-for-each-item

is the expected behavior of laravel sync, with attach we could get a single combined sql

[erikn69]

@drbyte I did make a branch for attach testing, but I don't know the implications of that change
https://github.com/erikn69/laravel-permission/tree/patch-28

[drbyte]

made a branch

Looking good. I've added some comments to it.

[drbyte]

@MohammedElattar A fix has been implemented. You can test it by upgrading to v6.0-dev.

[adam-james-scg]

@MohammedElattar A fix has been implemented. You can test it by upgrading to v6.0-dev.

Originally posted by @drbyte in #2419 (comment)

I believe it is this fix that is causing an Integrity Constraint Violation error for me.

When calling syncPermissions() in HasPermissions.php it is not doing any checks to remove duplicate entries. Laravel sync I believe handles this, but attach does not.

$user->syncPermissions($user->getPermissionsViaRoles());

In this code we're getting all of the permissions a user has due to their various roles and adding those permissions to the user. But some users in our database have two roles that both have a same permission. So when syncPermissions() calls givePermissionsTo() and does an attach() its error out.

$currentPermissions = $this->permissions->map(fn ($permission) => $permission->getKey())->toArray();

$this->permissions()->attach(array_diff($permissions, $currentPermissions), $teamPivot);
$model->unsetRelation('permissions');

NOTICE: PHP message: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '33-15-App\User' for key 'PRIMARY' (Connection: mysql, SQL: insert into user_has_permissions (model_type, permission_id, user_id) values (App\User, 18, 15), (App\User, 19, 15), (App\User, 24, 15), (App\User, 33, 15), (App\User, 33, 15), (App\User, 34, 15), (App\User, 34, 15), (App\User, 35, 15), (App\User, 36, 15), (App\User, 36, 15), (App\User, 37, 15), (App\User, 38, 15), (App\User, 39, 15), (App\User, 40, 15), (App\User, 40, 15))

[erikn69]

Hi @adam-james-scg, you could easily add array_unique,
but I made the PR #2574 so it doesn't happen to anyone again, please try it

[hubinjie]

I have SAAS permissions for multiple tenants. I want to update permissions all at once. There are 1,000 tenants, 5 roles, and 300 permissions. If I use syncPermissions, it will crash. It is faster to operate the data table directly.