whereHasPermisison with Teams throws SQLSTATE[23000] team_id is ambiguous

[DePalmo]

• Laravel Version: 10.48.28
• Laratrust Version: 8.3.2

Describe the bug
I'm using Teams and when I try to get users with specific permission with whereHasPermission, I get the following error: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'team_id' in where clause is ambiguous.

The code I'm using is:

        $query = User::query();

        if (empty($request->get('all'))) {
            $query->whereNot('id', $request->user()->id);
        }

        $query->whereHasPermission('archive.manage', $request->user()->organisation_id);

        return new IndexCollection($query->get());

But if I replace the whereHasPermisison with whereHas, it works:

        $query = User::query();

        if (empty($request->get('all'))) {
            $query->whereNot('id', $request->user()->id);
        }

        $query->where(function ($query) use ($request) {
            $query->whereHas('roles', function ($query) use ($request) {
                $query->where('roles.team_id', $request->user()->organisation_id)
                    ->whereHas('permissions', function ($query) use ($request) {
                        $query->where('name', 'archive.manage');
                    });
            })
                ->orWhereHas('permissions', function ($query) use ($request) {
                    $query->where('name', 'archive.manage')
                        ->where('permission_user.team_id', $request->user()->organisation->id);
                });
        });

        return new IndexCollection($query->get());

[Meeshalk]

Use ->toSql() to see the actual query. It feels like you are using .(dot) notation for your permission names, are you?

[DePalmo]

Correct. In my example above, you can see that the permission name is 'archive.manage', but that's not the issue. I added an example code that works and when examining it, it's clear that the issue is that when teams are enabled, it tries to filter roles and permissions with team_id, but because the query built does not specify in which table it should filter the team_id, it errors out.

This is the output of the ->toSql():

select * from `users` where (exists (select * from `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` where `users`.`id` = `role_user`.`user_id` and `role_user`.`user_type` = ? and exists (select * from `permissions` inner join `permission_role` on `permissions`.`id` = `permission_role`.`permission_id` where `roles`.`id` = `permission_role`.`role_id` and `name` = ? and `team_id` = ?) order by `roles`.`display_name` asc) or exists (select * from `permissions` inner join `permission_user` on `permissions`.`id` = `permission_user`.`permission_id` where `users`.`id` = `permission_user`.`user_id` and `permission_user`.`user_type` = ? and `name` = ? and `team_id` = ?)) and `users`.`deleted_at` is null and `users`.`team_id` = ? order by `users`.`name` asc

But if I then print out the whereHas:

select * from `users` where (exists (select * from `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` where `users`.`id` = `role_user`.`user_id` and `role_user`.`user_type` = ? and `roles`.`team_id` = ? and exists (select * from `permissions` inner join `permission_role` on `permissions`.`id` = `permission_role`.`permission_id` where `roles`.`id` = `permission_role`.`role_id` and `name` = ?) order by `roles`.`display_name` asc) or exists (select * from `permissions` inner join `permission_user` on `permissions`.`id` = `permission_user`.`permission_id` where `users`.`id` = `permission_user`.`user_id` and `permission_user`.`user_type` = ? and `name` = ? and `permission_user`.`team_id` = ?)) and `users`.`deleted_at` is null and `users`.`team_id` = ? order by `users`.`name` asc

Almost identical query, except that it added table names for team_id columns.
I went ahead and debugged the issue. The issue is in src/Traits/HasLaratrustScopes.php in function scopeWhereHasPermission. When I updated the code to this:

    public function scopeWhereHasPermission(
        Builder $query,
        string|array|BackedEnum $permission = '',
        mixed $team = null,
        string $boolean = 'and'
    ): Builder {
        $method = $boolean == 'and' ? 'where' : 'orWhere';

        return $query->$method(function ($query) use ($permission, $team) {
            $teamsStrictCheck = Config::get('laratrust.teams.strict_check');
            $method = is_array($permission) ? 'whereIn' : 'where';

            $query
            ->whereHas(
                'roles.permissions',
                fn ($permissionQuery) => $permissionQuery
                    ->$method('name', $permission)
                    ->when(
                        $team || $teamsStrictCheck,
                        fn ($q) => $q->where(
                            'roles.' . Team::modelForeignKey(),
                            Helper::getIdFor($team, 'team')
                        )
                    )
            )
            ->orWhereHas(
                'permissions',
                fn ($permissionQuery) => $permissionQuery
                    ->$method('name', $permission)
                    ->when(
                        $team || $teamsStrictCheck,
                        fn ($q) => $q->where(
                            'permission_user.' . Team::modelForeignKey(),
                            Helper::getIdFor($team, 'team')
                        )
                    )
            );
        });
    }

it worked. You'll notice that I added table names roles and permission_user in front of Team::modelForeignKey(). I do know that this could be more elegantly solved by pulling the table names from config file, but will leave that to you.