Ggtcf

I'm trying to get customer specific users who don't have an owner role, but it also skips users who don't have any role. Users can have one or multiple roles. I want to get all users either having multiple roles or no role at all, but if the user contains an owner role then only that user should be ignored.

Note: I am using spatie/laravel-permission which gets users roles from model has roles intermediate table

Here is my scope query

public function scopeForCompany(EloquentBuilder $query, string $customerId): EloquentBuilder
 
 $query->where(function (EloquentBuilder $q) 
 $q->doesntHave('roles');
 $q->orHas('roles');
 );

 $query->whereHas('roles', function (EloquentBuilder $q) 
 $q->whereNotIn('name', ['owner']);
 );


 return $query->where('customer_id', $customerId);;
 

here is the test

public function it_apply_query_scope_to_get_customer_specific_users_only(): void
 
 $model = new User;

 // create non customer users
 factory(User::class, 2)->create();

 $customer = factory(Customer::class)->create();
 foreach (['owner', 'admin', 'user'] as $role) 
 $role = factory(Role::class)->create(['name' => $role]);
 $user = factory(User::class)->create(['customer_id' => $customer->id]);
 $user->roles()->save($role);
 

 $scopedUsers = $model->newQuery()->forCompany($customer->id)->get();
 $nonScopedUsers = $model->newQuery()->get();

 static::assertCount(2, $scopedUsers); // Failed asserting that actual size 0 matches expected size 2.
 static::assertCount(5, $nonScopedUsers);
 

Debug: here is the row query:

"select * from `users` where (not exists (select * from `roles` inner join `model_has_roles` on `roles`.`id` = `model_has_roles`.`role_id` where `users`.`id` = `model_has_roles`.`model_uuid` and `model_has_roles`.`model_type` = ?) or exists (select * from `roles` inner join `model_has_roles` on `roles`.`id` = `model_has_roles`.`role_id` where `users`.`id` = `model_has_roles`.`model_uuid` and `model_has_roles`.`model_type` = ?)) and exists (select * from `roles` inner join `model_has_roles` on `roles`.`id` = `model_has_roles`.`role_id` where `users`.`id` = `model_has_roles`.`model_uuid` and `model_has_roles`.`model_type` = ? and `name` not in (?)) and `customer_id` = ? and `users`.`deleted_at` is null"

This is what i tried first but didn't worked

return $query->whereHas('roles', function (EloquentBuilder $query): void 
 $query->whereNotIn('name', ['owner']);
)->where('customer_id', $customerId);

Any help would be appreciated thanks

You need to do some Or logic for this to happen. I break the query up into 3 pieces.

The statement: "I want to get all users either having multiple roles"

$query->has('roles', '>=', 2);

Next you want all with no roles: "or no role at all".

$query->doesntHave('roles');

And lastly your query correctly filter out where the role cannot be the owner.

$query->whereHas('roles', function (EloquentBuilder $query): void 
 $query->whereNotIn('name', ['owner']);
)

Putting it all together doing something like, with a sub where query. To proper do the Or logic you want.

$query->where(function($builder)
 $builder->has('roles', '>=', 2);
 $builder->whereHas('roles', function (EloquentBuilder $query): void 
 $query->whereNotIn('name', ['owner']);
 )
);

$builder->orDoesntHave('roles');

In pseudo logical statements this would look something similar to like:

(roles.each.name != 'owner' && count(roles) >= 2) || empty(roles)

Let's see if this help your case, else post the toSql() of the builder and let's figure it out. It's a fairly complex query builder logic this is doing.