Nova doesn't correctly handle Scout results ordering

[dm-pf]

• Laravel Version: 10.41.0
• Nova Version: 4.32.11
• PHP Version: 8.2
• Database Driver & Version: MySQL 8
• Operating System and Version: #.#
• Browser type and version: #.#
• Reproduction Repository: https://github.com/###/###

Description:

When using a scout engine (e.g. meilisearch) to filter results - one would expect that whatever order the search engine returns - it's what's going to get painted in the UI. But this is not the case.

The problem lies within vendor/laravel/nova/src/Query/Builder.php::paginateFromScout:

protected function paginateFromScout(ScoutBuilder $queryBuilder, $perPage)
{

[...redacted...]

    /** @var array<int, string|int> $scoutResultKeys */
    $scoutResultKeys = $queryBuilder->keys()->all();

    /** @var \Illuminate\Database\Eloquent\Model&\Laravel\Scout\Searchable $model */
    $model = $this->resourceClass::newModel();

    /** @var Application $paginated */
    $paginated = tap(
        $model->queryScoutModelsByIds(
            $queryBuilder,
            $scoutResultKeys
        ),
        function ($query) {
            /** @var \Illuminate\Database\Eloquent\Builder $query */
            $this->originalQueryBuilder = $query;
        }
    )->simplePaginate($perPage);

    if (!$model->searchableUsing() instanceof PaginatesEloquentModels) {
        /** @var array<int|string, int> $objectIdPositions */
        $objectIdPositions = collect($scoutResultKeys)->values()->flip()->all();

        $paginated->setCollection(
            $paginated->getCollection()
                ->sortBy(function ($model) use ($objectIdPositions) {
                    return $objectIdPositions[$model->getScoutKey()];
                }, SORT_NUMERIC)->values()
        );
    }

    return [$paginated, $this->getCountForPagination(), false];
}

$scoutResultKeys is the array of IDs in correct order from Scout. Lets imagine it contains the following: [20, 21, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10.......]

After calling:

$model->queryScoutModelsByIds(
    $queryBuilder,
    $scoutResultKeys
)

Everything is scrambled by the SQL engine - and we get a Collection with this order [1, 2, 3, 4.....]. As we're aware of this, we try to re-order everything manually through the $paginator->setCollection(getCollection()) logic, but this is not enough. Why? Because at this point, the ->simplePagine($perPage = 10) has already gotten only the first 10 results from SQL (from ID:1 to ID10).

As you saw before, ID:20 and ID:21 must be the first two items, but they will never show up in the first Nova resource page if perPage is 10. If we change perPage to 25 - then the result show up immediately in the first place.

Ideally, we should be sorting what Scout gave us - at the SQL level - and this is pretty straightforward with MySQL using ORDER BY FIELD(). For our own project, we have modified this file:

Before:

$paginated = tap(
    $model->queryScoutModelsByIds(
        $queryBuilder,
        $scoutResultKeys
    ),
    function ($query) {
        /** @var \Illuminate\Database\Eloquent\Builder $query */
        $this->originalQueryBuilder = $query;
    }
)->simplePaginate($perPage);

After:

$paginated = tap(
    $model->queryScoutModelsByIds(
        $queryBuilder,
        $scoutResultKeys
    )->orderByRaw('FIELD(id, '.implode(',', $scoutResultKeys).')'),
    function ($query) {
        /** @var \Illuminate\Database\Eloquent\Builder $query */
        $this->originalQueryBuilder = $query;
    }
)->simplePaginate($perPage);

This way, results are sorted on the SQL level and simplePaginate() will work correctly.

Detailed steps to reproduce the issue on a fresh Nova installation:

[crynobone]

The suggested changes doesn't work in Postgres and MSSQL.

[dm-pf]

That is unfortunately the case indeed. I shared my "workaround" if others using mysql hit this issue - but indeed a better (more generic) solution should be implemented.

[goldmerc]

@dm-pf Thanks for your post. I'm having the same experience. I tried your work around but it's not working for me. Are you still using your method? I'm on Nova 5.7.5 maybe things have changed?