Laravel迁移至PostgreSQL：布尔字段与JSON排序问题排查

我正尝试将使用spatie/laravel-translatable 6.3的Laravel应用从MySQL 8迁移至PostgreSQL 14。

数据库表定义 #

Schema::create('quiz_categories', function (Blueprint $table) {
        $table->id();
        $table->json('name')->comment('多语言支持字段');
        $table->boolean('active')->default(false);
});

模型代码（app/Models/QuizCategory.php） #

class QuizCategory extends Model
{
    use HasTranslations;

    protected $table = 'quiz_categories';
    public $translatable = ['name'];
    protected $primaryKey = 'id';
    public $timestamps = false;
    protected $casts = [
        'active' => 'boolean',
        'name' => 'json'
    ];
    protected $fillable = ['name', 'active'];

    public function scopeGetByActive($query, $active = null)
    {
        if (! isset($active)) {
            return $query;
        }

        return $query->where($this->table . '.active', (bool)$active);
    }
}

初始查询报错 #

执行以下查询时：

$quizCategories = QuizCategory::orderBy('name', 'desc')
    ->getByActive($filterActive)->get();

出现错误：

SQLSTATE[42883]: Undefined function: 7 ERROR: could not identify an ordering operator for type json LINE 1: ...s" where "quiz_categories"."active" = $1 order by "name" des... ^ HINT: Use an explicit ordering operator or modify the query.
SELECT * FROM "quiz_categories" WHERE "quiz_categories"."active" = 1 ORDER BY "name" DESC

测试SQL查询后，我发现需要：

• a) 将查询改写为"quiz_categories"."active" = true，但不确定如何用Eloquent实现；
• b) 移除ORDER BY "name" DESC，但想知道如何对JSON字段进行排序。

MySQL可以执行原代码，但不确定是否正确。

更新尝试1 #

重新处理问题时，执行以下代码：

$quizCategories = QuizCategory::query()
    ->getByActive()
    ->orderByRaw('name->"$.en"', 'desc')
    ->get();

出现错误：

SQLSTATE[42703]: Undefined column: 7 ERROR: column "$.en" does not exist LINE 1: select * from "quiz_categories" order by name->"$.en" ^
SELECT * FROM "quiz_categories" ORDER BY name->"$.en"

更新尝试2 #

尝试以下查询：

$quizCategories = QuizCategory::query()
    ->getByActive()
    ->orderBy(DB::raw("json_extract('name', '$.en')"), 'desc')
  ->get();

错误：

function json_extract(unknown, unknown) does not exist

随后改用json_extract_path函数：

$quizCategories = QuizCategory::query()
    ->getByActive()
    ->orderBy(DB::raw("json_extract_path('name', '$.en')"), 'desc')
  ->get();

错误：

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type json LINE 1: ...from "quiz_categories" order by json_extract_path('name', '$... ^ DETAIL: Token "name" is invalid. CONTEXT: JSON data, line 1: name

更新尝试3 #

使用PostgreSQL 14，查阅官方文档后，文档显示存在json_extract_path函数，且有示例：

jsonb -> text → jsonb
Extracts JSON object field with the given key.
'{"a": {"b":"foo"}}'::json -> 'a' → {"b":"foo"}

看起来可以使用'name->"$.en"'写法，但使用时仍报错。

更新尝试4 #

执行以下代码：

$quizCategories = QuizCategory::query()
    ->getByActive()
    ->orderBy(DB::raw("name->>en"), 'desc')
    ->get();

错误：

Undefined column: 7 ERROR: column "en" does not exist LINE 1: select * from "quiz_categories" order by name->>en desc ^

1. 布尔值查询适配 #

PostgreSQL中布尔类型判断使用true/false，Eloquent在处理布尔参数时会自动适配数据库类型。你可以优化作用域代码，让逻辑更清晰：

public function scopeGetByActive($query, $active = null)
{
    if ($active === null) {
        return $query;
    }

    // 直接传入布尔值，Eloquent会自动转换为PostgreSQL支持的true/false
    return $query->where('active', (bool)$active);
}

注：原代码中的(bool)$active已经能让Eloquent正确绑定参数，之前错误日志中的=1是参数绑定的显示形式，实际执行时PostgreSQL会正确识别布尔值，这个不是核心报错原因。

2. JSON字段排序的正确写法 #

PostgreSQL中无法直接对JSON类型字段排序，必须提取出JSON中的文本值再排序，核心注意两点：

• 使用->>操作符获取文本值（而非->，后者返回JSON类型）
• 键名必须用单引号包裹

写法1：使用orderByRaw #

$quizCategories = QuizCategory::query()
    ->getByActive($filterActive)
    ->orderByRaw('name->>\'$.en\' DESC')
    ->get();

PHP字符串中需用\'转义单引号，确保PostgreSQL能正确识别键名。

写法2：使用DB::raw配合orderBy #

$quizCategories = QuizCategory::query()
    ->getByActive($filterActive)
    ->orderBy(DB::raw('name->>\'$.en\''), 'desc')
    ->get();

之前尝试失败的原因 #

• 更新尝试1：orderByRaw的第二个参数是绑定参数，不是排序方向；且用了->返回JSON类型，无法排序，应改用->>。
• 更新尝试2：错误地给字段名name加了单引号，导致PostgreSQL把它当成JSON字符串处理，而非数据库字段；且json_extract返回JSON类型，需用json_extract_path_text转成文本。
• 更新尝试4：en未加单引号，PostgreSQL将其识别为列名，导致“column en does not exist”错误。

多语言动态排序 #

如果要根据当前应用语言动态排序，可以这样写：

$lang = app()->getLocale();
$quizCategories = QuizCategory::query()
    ->getByActive($filterActive)
    ->orderByRaw("name->>'$.{$lang}' DESC")
    ->get();

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