AND-condition within a filter

[christheoalex]

Hey there!

taking the following example:

// GET /users?filter[name]=seb,freek

$users = QueryBuilder::for(User::class)
    ->allowedFilters(['name'])
    ->get();

// $users will contain all users that contain "seb" OR "freek" in their name

What would be a proper way to query all users that contain "seb" AND "freek" in their name?

Best,
Chris

[AlexVanderbist]

Hi there! Usually different filters are all applied using AND conditions. Under the hood they are simple where methods being called on the query and this is the default behaviour. However, in your example you're using a partial filter (the default for allowedFilters) with an array of names. In this case we're using orWhere behind the scenes because it seemed like a good default: https://github.com/spatie/laravel-query-builder/blob/master/src/Filters/FiltersPartial.php#L32

If you'd want to use a regular where instead, you can create a custom NameFilter or that works exactly like the default FiltersPartial filter but changes that orwhere to where. Good luck! 👍

[jolora]

Sorry to rake up an old conversation but this is something I'm also dealing with at the moment. Rather than relying on orWhere seeming like a good default, would it not be best to provide the option on the filter as a parameter? It's a very common scenario.

[AlexVanderbist]

@jolora feel free to send a PR with some example code/tests/ideas. IIRC the biggest issue with having the option for OR/AND filters is figuring out how to apply the order of operations in a sensible way. E.g.:

• allowed filter for type (AND)
• allowed filter for name (OR)
• allowed filter for email (OR)
• allowed filter for country (AND)

GET users?filter[name]=seb&filter[email]=seb&filter[country]=be

You'd expect the example request above ^ to work as follow: (name = seb OR email = seb) AND country = be which makes sense. The issue becomes more interesting if the request looks like this:

GET users?filter[type]=admin&filter[name]=seb&filter[email]=seb

I would expect type = admin AND (name = seb OR email = seb) but by default Laravel will create a query that works like this: (type = admin AND name = seb) OR email = seb.

So tl;dr: combining AND / OR in the same query builder is hard and unpredictable without a way to group conditions (especially with optional filters).

[Floppy012]

@AlexVanderbist

Maybe I'm missing something. How about allowing multiple calls to allowedFilters. And defining how the filters shall be combined there? E.g.:

QueryBuilder::for(Model::class)
    ->allowedFilters(['type', 'country'])
    ->allowedFilters(['name', 'email'], QueryBuilder::FILTER_OR);

Every allowedFilters call would use an advanced where query and apply the filters in there chaining them by default with AND and if specified (like in the example) with OR.

For a request like GET users?filter[name]=seb&filter[email]=seb&filter[country]=be&filter[type]=eb the query would end up like this:

SELECT * FROM model WHERE (type LIKE '%eb%' AND country LIKE '%be%') AND (name LIKE '%seb%' OR email LIKE '%seb%')

For even more complex scenarios allowedFilters would accept a third parameter that allows to change the way that the allowFilters call will be added to the query. E.g.:

QueryBuilder::for(Model::class)
    ->allowedFilters(['type', 'country'])
    ->allowedFilters(['name', 'email'], QueryBuilder::FILTER_AND, QueryBuilder::FILTER_OR);

Query would look like this:

SELECT * FROM model WHERE (type LIKE '%eb%' AND country LIKE '%be%') OR (name LIKE '%seb%' AND email LIKE '%seb%')

My current workaround

Since I need this, I have currently a workaround in place that looks like this:

$queryBuilder = QueryBuilder::for(User::class);

$queryBuilder->where(function (Builder $query) {
    $queryBuilderRequest = app(QueryBuilderRequest::class);
    $queryBuilderRequest->filters()
        ->filter(fn ($_, $key) => in_array($key, ['full_name', 'email', 'phone'])
        ->each(fn ($val, $key) => $query->orWhere($key, 'LIKE', "%$val%"));
});

if (($groupId = $request->query('groupId')) !== null) {
    $queryBuilder->where('group_id', $groupId);
}

[camohub]

The ->allowedFilters(['name', 'email'], QueryBuilder::FILTER_OR); is a good idea.
But it seems it is only a fiction.
But why if the basic usage only suppose the all QB filters will be in one

where(function() {
})