Laravel Scout always appends ORDER BY id at the end of the query

[JakubSzczesniak]

Scout Version

10.10

Scout Driver

Database

Laravel Version

11.0

PHP Version

8.2

Database Driver & Version

MariaDB 10

SDK Version

Meilisearch CLI Version

Description

When using Laravel Scout for querying, it has been observed that it always appends an additional ORDER BY id clause to the end of the generated SQL query. This leads to unexpected issues with sorting, especially when performing joins, as the additional sort by id can cause bugs in the query execution.

Steps To Reproduce

Step 1: Configure .env

SCOUT_DRIVER=database

Step 2: Create models

namespace App\Models;

use Illuminate\Database\Eloquent\Concerns\HasUuids;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Laravel\Scout\Searchable;

class ModelA extends Model
{
    use HasFactory, HasUuids, Searchable;

    protected $table = 'model_a';

    protected $fillable = [
        'name',
    ];

    public function modelB(): BelongsTo
    {
        return $this->belongsTo(ModelB::class);
    }

    public function toSearchableArray(): array
    {
        return [
            'name' => '',
            'model_b.name' => '',
        ];
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Concerns\HasUuids;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Laravel\Scout\Searchable;

class ModelB extends Model
{
    use HasFactory, HasUuids, Searchable;

    protected $table = 'model_b';

    protected $fillable = [
        'name',
    ];

    public function modelA(): HasMany
    {
        return $this->hasMany(ModelA::class);
    }

    public function toSearchableArray(): array
    {
        return [
            'name' => '',
        ];
    }
}

Step 3: Create migrations:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('model_b', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->string('name');
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('model_b');
    }
};

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('model_a', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->string('name');
            $table->foreignUuid('model_b_id')->constrained('model_b')->cascadeOnDelete();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('model_a');
    }
};

Step 4: Create Controller:

<?php

namespace App\Http\Controllers;

use App\Models\ModelA;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;

class IndexModelAController extends Controller
{
    public function __invoke(Request $request)
    {
        return ModelA::search($request->input('filter.search'))
            ->query(function (Builder $query) use ($request) {
                $query
                    ->join('model_b', 'model_a.model_b_id', '=', 'model_b.id')
                ;
            })
            ->orderBy('model_b.name', 'desc')
            ->paginate()
        ;
    }
}

Issue:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in order clause is ambiguous (Connection: mariadb, SQL: select * from `model_a` inner join `model_b` on `model_a`.`model_b_id` = `model_b`.`id` order by `model_b`.`name` desc, `id` desc limit 15 offset 0)

Possible problem: \Laravel\Scout\Engines\DatabaseEngine::simplePaginateUsingDatabase should be changed from:

    public function simplePaginateUsingDatabase(Builder $builder, $perPage, $pageName, $page)
    {
        return $this->buildSearchQuery($builder)
            ->when($builder->orders, function ($query) use ($builder) {
                foreach ($builder->orders as $order) {
                    $query->orderBy($order['column'], $order['direction']);
                }
            })
            ->when(! $this->getFullTextColumns($builder), function ($query) use ($builder) {
                $query->orderBy($builder->model->getScoutKeyName(), 'desc');
            })
            ->simplePaginate($perPage, ['*'], $pageName, $page);
    }

into:

    public function simplePaginateUsingDatabase(Builder $builder, $perPage, $pageName, $page)
    {
        return $this->buildSearchQuery($builder)
            ->when($builder->orders, function ($query) use ($builder) {
                foreach ($builder->orders as $order) {
                    $query->orderBy($order['column'], $order['direction']);
                }
            })
            ->when(! $this->getFullTextColumns($builder) && !$builder->orders, function ($query) use ($builder) {
                $query->orderBy($builder->model->getScoutKeyName(), 'desc');
            })
            ->simplePaginate($perPage, ['*'], $pageName, $page);
    }

[crynobone]

Feel free to submit PR for Taylor to consider.