Add search filter to query multiple columns/related columns from one filter

[AlexVanderbist]

Basically this:

QueryBuilder(User::class)
    ->allowedFilters([
        Filter::search('q', ['first_name', 'last_name', 'address.city', 'address.country']),
    ]);

Bonus points for splitting the search query string into multiple parts and searching those as well. E.g. searching for "John Doe Antwerp" should still yield results by splitting the string into "john", "doe" and "antwerp" and searching for these strings independently.

[dominikb]

Would, by the given example, a request like /users?filter[q]=John+Doe+Antwerp result in three where like clauses (for each space-separated part in the query string) per searchable field?

Or would each space-separated query part be applied to the allowed columns in order?

1. Case

SELECT * FROM 
  users 
WHERE 
  users.first_name LIKE "%John%" 
  OR 
  users.last_name LIKE "%John%"
# And so on ...

2. Case

SELECT * FROM 
  users 
  JOIN addresses ON addresses.user_id = users.id 
WHERE 
  users.first_name LIKE "%John%" 
  OR 
  users.last_name LIKE "%Doe%" 
  OR 
  addresses.city LIKE "%Antwerp%" 
# No where clause for country as it was not supplied

[axelitus]

I was just looking into a search capability in this package (I was already starting to have fun with the code). I was thinking more like introducing a new query keyword and specific classes because as I see it, although certainly similar, filter !== search. In the first case, we are narrowing down a resultset, whereas in the latter case we are in some way expanding it (as a matter of speech).

I was thinking about something like this:

QueryBuilder::for(User::class)
    ->allowedSearchesIn([
        'first_name', 'last_name', 'address.city', 'address.country'
    ]);

and allowing the QueryString keyword to take these forms:

// Searches in all allowed columns loosely: OR WHERE column LIKE '%term%' ...
$search = 'search=term';
// Searches in all allowed columns for begins with: OR WHERE column LIKE 'term%' ...
$search = 'search:begins=term';
// Searches in all allowed columns for ends with: OR WHERE column LIKE '%term' ...
$search = 'search:ends=term';
// Searches in all allowed columns exactly: OR WHERE column = 'term' ...
$search = 'search:exact=term';

// Searches in column field loosely: OR WHERE `field` LIKE '%term%'
$search = 'search[field]=term';
// Searches in column field for begins with: OR WHERE `field` LIKE 'term%'
$search = 'search:begins[field]=term';
// Searches in column field for ends with: OR WHERE `field` LIKE '%term'
$search = 'search:ends[field]=term';
// Searches in column field exactly: OR WHERE `field` = 'term'
$search = 'search:exact[field]=term';

// Searches in the given columns loosely: OR WHERE `field1` LIKE '%term%' OR WHERE `field2` LIKE '%term%'
$search = 'search[field1,field2]=term';
// Searches in the given columns for begins with: OR WHERE `field1` LIKE 'term%' OR WHERE `field2` LIKE 'term%'
$search = 'search:begins[field1,field2]=term';
// Searches in the given columns for ends with: OR WHERE `field1` LIKE '%term' OR WHERE `field2` LIKE '%term'
$search = 'search:ends[field1,field2]=term';
// Searches in the given columns exactly: OR WHERE `field1` = 'term'  OR WHERE `field2` = 'term'
$search = 'search:exact[field1,field2]=term';

Just like with filters, there would be some classes that handle these cases: SearchLoose (default), SearchBegins, SearchEnds, SearchExact and also a Search::custom(...) method to extend the search capabilities passing a Search object.

I didn't think of splitting the given term, maybe like:

// Searches in all allowed columns loosely splitting the term by white space: OR WHERE column LIKE '%term%' OR WHERE column LIKE '%with%' OR WHERE column LIKE '%spaces%' ...
$search = 'search:split=term with spaces';

// Searches in column field loosely splitting the term by white space: OR WHERE `field` LIKE '%term%' OR WHERE `field` LIKE '%with%' OR WHERE `field` LIKE '%spaces%'
$search = 'search:split[field]=term with spaces';

// Searches in column field loosely splitting the term by white space: OR WHERE `field1` LIKE '%term%' OR WHERE `field1` LIKE '%with%' OR WHERE `field1` LIKE '%spaces%' OR WHERE `field2` LIKE '%term%' OR WHERE `field2` LIKE '%with%' OR WHERE `field2` LIKE '%spaces%'
$search = 'search:split[field1,field2]=term with spaces';

Let me know what you think.

[llstarscreamll]

@axelitus that would be awesome!! Are there any progress on this proposal?

[axelitus]

Hey @llstarscreamll. I think it would be powerful. The use case I want to tackle is a server-side processing VueJS debatable that would search on specific model properties.

There is no real progress as of today. I came with the idea just on friday thursday (I don't even know in which day I'm living on hehe) and started digging inside the code to see how best to add this.

I wanted to define the queystring syntax first (to be as simple es possible but flexible enough) to see what needs to be done to parse it correctly and add the necessary querybuilder calls.

[bzelba]

This feature would be such a painkiller 👍

[axelitus]

Hi everyone, sorry for the delay but these weeks have been really complicated for me. I have officially started to tackle this... expect a PR soon...

Update: for everyone that wants to follow before I actually send the PR here's the branch: https://github.com/axelitus/laravel-query-builder/tree/add-search-capabilities

It seems to work pretty well as of now with partial, exact, begins and ends. No split implementation still.

[axelitus]

For the status of my proposed implementation please refer to this comment.

[vsg24]

If anyone is wondering about how to do search, here's how to do it.

[AlexVanderbist]

Closing this for now as it's trivial to add a custom search filter that works exactly like you want it to work

[gutofurlan]

Hello, I'm having a search method error not being recognized by the Filter, you know what it can be?

[shoxton]

Whats the current status of this pr?