Join in indexQuery and filter apply

[exshapoval]

Hi

I have custom indexQuery

       $resourceTable = 'soft';
        $user = Auth::user();

        $orderBy = $request->get('orderBy');
        $dir = $request->get('orderByDirection');

        $query->select(DB::raw("{$resourceTable}.*"));

        if (!$user->isGlobalAdmin) {
            $roles = json_decode($user->getRoleNames());
            $rolesArray = [];
            foreach ($roles as $roleName) {
                $role = json_decode(Role::findByName($roleName));
                $rolesArray[] = $role->id;
            }

            $query->join('model_has_roles', 'model_id', '=', "{$resourceTable}.id")
                ->where('model_type', '=', 'App\Models\Soft')
                ->whereIn('role_id', $rolesArray);
        }

When I load index page of resource sql is

select soft.* from `soft` 
inner join `model_has_roles` on `model_id` = `soft`.`id` 
where `model_type` = 'App\\Models\\Soft' and `role_id` in (2, 3)

And I have filter with roles.

code for apply is

        $roles = $value;
        $rolesArray = [];
        foreach ($roles as $roleName) {
            $role = json_decode(Role::findByName($roleName));
            $rolesArray[] = $role->id;
        }

        return $query->whereIn('role_id', $rolesArray);

When I set one in filter I get sql

select soft.* from `soft` 
inner join `model_has_roles` on `model_id` = `soft`.`id` 
where `role_id` in (3) and `model_type` = App\\Models\\Soft and `role_id` in (2, 3)

2 times role_id IN, how to correct it to only one time?

[crynobone]

Don't use join, instead use add sub select query if needed.

[exshapoval]

why its better?
And help me, please, how to use sub query

[exshapoval]

Ok, I use sub query. But I still see in where one part from filter and one from indexQuery, how I can fix it?
Maybe some way to rewrite all query for filter? Or determine in indexQuery if filter selected?