Laravel多语言项目中，如何在MySQL中按JSON字段排序？

问题原因 #

MySQL对JSON类型字段直接使用orderBy('name')时，会按照JSON的原始字符串格式排序，而非解析后指定语言字段的文本值，因此无法得到预期的排序结果。PostgreSQL的name->>'en'是正确解析JSON字段内en属性并按文本排序的语法，但MySQL的JSON字段访问语法与PostgreSQL不同。

具体解决方案 #

方案1：使用MySQL原生JSON字段排序语法 #

直接在orderBy中传入MySQL支持的JSON字段访问语法，MySQL 8.0.14及以上版本支持->>运算符（等同于JSON_UNQUOTE(JSON_EXTRACT())），写法如下：

// 直接使用字符串语法
Model::orderBy('name->>"$.en"')->get();

// 或用DB::raw确保语法被正确解析
use Illuminate\Support\Facades\DB;
Model::orderBy(DB::raw('name->>"$.en"'))->get();

如果使用更低版本MySQL（不推荐），可使用完整函数写法：

Model::orderBy(DB::raw('JSON_UNQUOTE(JSON_EXTRACT(name, "$.en"))'))->get();

方案2：利用spatie/laravel-translatable内置方法 #

spatie扩展提供了orderByTranslation方法，会自动适配MySQL和PostgreSQL的语法差异，无需手写原生SQL：

Model::orderByTranslation('name', 'en')->get();

该方法支持指定排序方向：

Model::orderByTranslation('name', 'en', 'desc')->get();

方案3：封装通用排序范围（多数据库兼容场景推荐） #

如果需要在多个模型或多处复用，可在模型中定义局部范围，自动适配数据库类型：

use Illuminate\Database\Eloquent\Builder;

class YourModel extends Model
{
    use \Spatie\Translatable\HasTranslations;

    protected $translatable = ['name'];

    public function scopeOrderByTranslated(Builder $query, string $field, string $locale = 'en', string $direction = 'asc')
    {
        $driver = $query->getConnection()->getDriverName();
        
        if ($driver === 'mysql') {
            $query->orderBy(DB::raw("{$field}->>\"$.{$locale}\""), $direction);
        } elseif ($driver === 'pgsql') {
            $query->orderBy("{$field}->>'{$locale}'", $direction);
        }
        
        return $query;
    }
}

使用时直接调用：

YourModel::orderByTranslated('name', 'en')->get();

验证方法 #

执行排序后，可通过toSql()方法查看生成的SQL语句，确认JSON字段解析是否正确：

dd(YourModel::orderByTranslation('name', 'en')->toSql());

MySQL下生成的SQL类似：

select * from `your_models` order by `name`->>"$.en" asc

PostgreSQL下生成的SQL类似：

select * from "your_models" order by "name"->>'en' asc

内容的提问来源于stack exchange，提问作者mstdmstd