Split out custom filter from other filters in Laravel Query Builder SQL query

I'm using Spatie's Laravel Query Builder to handle filtering for a large table, and have created a custom filter:

use Illuminate\Database\Eloquent\Builder;
use Spatie\QueryBuilder\Filters\Filter;

class NameMemberNumberFilter implements Filter
{
    /**
     * @inheritDoc
     */
    public function __invoke(Builder $query, $value, string $property)
    {
        $query->where('last_name', 'like', "%{$value}%")
            ->orWhere('first_name', 'like', "%{$value}%")
            ->orWhere('member_number', 'like', "%{$value}%");
    }
}

and have implemented it in my controller like so:

    public function index()
    {
        return Inertia::render('Users/Index', [
            'users' => QueryBuilder::for(User::class)
                ->defaultSort('member_number')
                ->allowedSorts(['last_name', 'member_number'])
                ->allowedFilters(AllowedFilter::custom('name_number', new NameMemberNumberFilter),
                    AllowedFilter::exact('status'),
                    AllowedFilter::exact('member_type'))
                ->paginate(20)
                ->withQueryString()
        ]);
    }

The problem is that the way the package groups my filters into one big query instead of separate clauses in the query

SELECT * FROM `users` 
WHERE (`last_name` like '%Jones%' or `first_name` like '%Jones%' or `member_number` like '%Jones%' and `users`.`status` = 'Active') 
and `users`.`deleted_at` IS NULL 
ORDER BY `member_number` ASC
 LIMIT 20 
offset 0;

However, this gives me incorrect results, more than I should have. If I change the query like this

SELECT * FROM `users` 
WHERE (`last_name` like '%Jones%' or `first_name` like '%Jones%' or `member_number` like '%Jones%')  
and `users`.`status` = 'Active' 
and `users`.`deleted_at` IS NULL 
ORDER BY `member_number` ASC 
LIMIT 20 
offset 0;

then I get the correct results (note the difference is the and users.status = 'Active' is outside the WHERE clause for the custom filter). if I was using the Laravel query builder, that would be simple enough to do. However, since the QueryBuilder is generating the query, is there a way to override it to get the correct query to run?

I tried this:

class NameMemberNumberFilter implements Filter
{
    /**
     * @inheritDoc
     */
    public function __invoke(Builder $query, $value, string $property)
    {
        $query->orWhere(
            fn (Builder $builder) => $query
                ->where('last_name', 'like', "%{$value}%")
                ->orWhere('first_name', 'like', "%{$value}%")
                ->orWhere('member_number', 'like', "%{$value}%")
        );
    }
}

but it didn't make a difference.

LaryAI

3 years ago

Level 58

One solution to this problem is to modify the custom filter to add the additional conditions to the main query instead of adding them to the filter query. This can be done by using the when method to conditionally add the status and deleted_at conditions to the main query. Here's an example:

class NameMemberNumberFilter implements Filter
{
    /**
     * @inheritDoc
     */
    public function __invoke(Builder $query, $value, string $property)
    {
        $query->where(function (Builder $query) use ($value) {
            $query->where('last_name', 'like', "%{$value}%")
                ->orWhere('first_name', 'like', "%{$value}%")
                ->orWhere('member_number', 'like', "%{$value}%");
        })->when(request('status'), function (Builder $query, $status) {
            $query->where('status', $status);
        })->whereNull('deleted_at');
    }
}

In this modified filter, we use a closure to group the last_name, first_name, and member_number conditions together. We then use the when method to conditionally add the status condition to the main query if it is present in the request. Finally, we add the deleted_at condition to the main query outside of the closure.

With this modification, the custom filter will add its conditions to the main query instead of creating a separate filter query, and the status and deleted_at conditions will be added to the main query as well.

Please or to participate in this conversation.