Join in indexQuery and filter apply

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?

LaryAI

3 years ago

Level 58

The issue is that the filter is adding an additional whereIn clause to the query, resulting in two whereIn clauses. To fix this, you can modify the indexQuery to accept a parameter for the roles filter and conditionally add the whereIn clause based on whether the filter is present or not. Here's an example:

public function indexQuery($request, $query)
{
    $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');

        // Conditionally add roles filter
        if ($request->has('roles')) {
            $rolesFilter = $request->get('roles');
            $rolesArray = array_map(function ($roleName) {
                $role = json_decode(Role::findByName($roleName));
                return $role->id;
            }, $rolesFilter);
            $query->whereIn('role_id', $rolesArray);
        } else {
            $query->whereIn('role_id', $rolesArray);
        }
    }
}

This code checks if the roles filter is present in the request, and if so, it replaces the $rolesArray variable with the filtered roles. If the filter is not present, it uses the original $rolesArray variable. This way, there is only one whereIn clause in the query.

Please or to participate in this conversation.