Search Column querying more than where()

[RonMelkhior]

Hi,

I recently implemented my own version of SearchableText, called SearchableMulti which is essentially the same but with support for multiple columns. Due to MariaDB behavior, I have to explicitly order it by the best match - like so:

/**
 * Apply the search.
 *
 * @param  \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Eloquent\Relations\Relation  $query
 * @param  string  $search
 * @return \Illuminate\Database\Eloquent\Builder
*/
public function __invoke($query, $search, string $connectionType, string $whereOperator = 'orWhere')
{
    if (in_array($connectionType, ['mysql', 'pgsql'])) {
        $query
            ->selectRaw('*, MATCH('.implode(', ', $this->columnName($query)).') AGAINST (? IN NATURAL LANGUAGE MODE) as _search_relevance', [$search])
            ->{$whereOperator.'FullText'}(
                $this->columnName($query), $search
            )
            ->orderBy('_search_relevance', 'desc');
    }

    return $query;
}

The issue is - the Nova Laravel\Nova\Query\Search class runs each column's invoke method inside a ->where() scope.

    /**
     * Get the raw results of the search.
     *
     * @param  class-string<\Laravel\Nova\Resource>  $resourceClass
     * @param  array<int, string|\Laravel\Nova\Query\Search\Column>  $searchColumns
     * @return mixed
     */
    public function handle($resourceClass, array $searchColumns)
    {
        return $this->queryBuilder->where(function ($query) use ($searchColumns) { // <---
            $connectionType = $query->getModel()->getConnection()->getDriverName();

            collect($searchColumns)
                ->each(function ($column) use ($query, $connectionType) {
                    if ($column instanceof Column || (! is_string($column) && is_callable($column))) {
                        $column($query, $this->searchKeyword, $connectionType);
                    } else {
                        Column::from($column)->__invoke($query, $this->searchKeyword, $connectionType);
                    }
                });
        });
    }

Meaning the selectRaw and the orderBy calls in my SearchableMulti column are ignored. To overcome this - I recreated the Search class and created a trait to include in each resource that uses SearchableMulti.

I'd like to hear if this is the right way to approach sorting search results when using a custom column, and if it is - if perhaps the Search class can support queries that aren't where() by default

[crynobone]

This seems more suitable as Laravel Scout driver unless you want to make a replacement to Laravel\Nova\Query\Search using Laravel Container.