How to completely override the search query for Select while also using relationship?
By default the search query uses LIKE and i would like to use full text search due to the amount of records i have however when using relationship
modifyQueryUsing option you are still left with the LIKE statement in query. Trying to use getSearchResultsUsing and relationship seems to just throw an error
foreach() argument must be of type array|object, null given when trying to do a search. The relationship authors is BelongsToMany
This code fails with the error:
Select::make('authors')
->native(false)
->searchable()
->getSearchResultsUsing(
fn (string $search): array => Author::query()
->whereFullText(['name'], $search, ['mode' => 'boolean'])
->orderByDesc('views')
->get()
->pluck('name', 'id')
)
->multiple()
->relationship(
name: 'authors'
)
//Just for the sake of example, this is/would be dynamic
->getOptionLabelsUsing(fn (array $values): array => [1 => 'Author #1', 2 => 'Author #2'])
->searchDebounce(300)
This code still contains the original LIKE in query to db
Select::make('authors')
->native(false)
->searchable()
->multiple()
->relationship(
name: 'authors',
titleAttribute: 'name',
modifyQueryUsing: fn (Builder $query, ?string $search) => $query
->when($search, fn ($q) => $q->whereFullText(['name'], $search, ['mode' => 'boolean']))
->orderByDesc('views')
)
->searchDebounce(300)Solution:Jump to solution
Seems i found the solution by looking at the source code of Select.
When you provide an empty array to searchable it won't apply the automatic LIKE due to no columns defined.
This Select won't apply the LIKE in final db query
...
3 Replies
please provide the code you are using
Note, you can use searchable() and add the query into here to override the original like query
i.e. searchable(fn($query) => $query->where()) etc
While searchable does have the query it does not resolve the actual search term.
Solution
Seems i found the solution by looking at the source code of Select.
When you provide an empty array to searchable it won't apply the automatic LIKE due to no columns defined.
This Select won't apply the LIKE in final db query
Select::make('authors')
->native(false)
->searchable([])
->multiple()
->relationship(
name: 'authors',
titleAttribute: 'name',
modifyQueryUsing: fn (Builder $query, ?string $search) => $query
->when($search, fn ($q) => $q->whereFullText(['name'], $search, ['mode' => 'boolean']))
->orderByDesc('views')
)
->searchDebounce(300)