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

modifyQueryUsing

option you are still left with the LIKE statement in query. Trying to use

getSearchResultsUsing

getSearchResultsUsing

and relationship seems to just throw an error

foreach() argument must be of type array|object, null given

foreach() argument must be of type array|object, null given

when trying to do a search. The relationship

authors

authors

BelongsToMany

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)

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)

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

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)

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)

Jump to solution

toeknee•10/3/24, 3:52 PM

please provide the code you are using

toeknee•10/3/24, 3:53 PM

Note, you can use searchable() and add the query into here to override the original like query

toeknee•10/3/24, 3:53 PM

i.e. searchable(fn($query) => $query->where()) etc

Ttoeknee please provide the code you are using

GLOP•10/3/24, 4:01 PM

While searchable does have the query it does not resolve the actual search term.

Solution

GL•10/3/24, 4:24 PM

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)

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)