Laravel查询构建器使用COALESCE返回空值问题求助

问题描述 #

有一段MySQL查询可以正常返回subject和body_html的有效值：

SELECT
    translations.id,
    COALESCE(locale.subject, fallback.subject) as subject,
    COALESCE(locale.body_html, fallback.body_html) as body_html 
FROM
    translations
LEFT JOIN
    translations AS locale
    ON locale.translatable_id = translations.translatable_id
    AND locale.translatable_type = translations.translatable_type
    AND locale.locale = "fr" 
LEFT JOIN
    translations AS fallback
    ON fallback.translatable_id = translations.translatable_id
    AND fallback.translatable_type = translations.translatable_type
    AND fallback.locale = "en" 
WHERE
    translations.translatable_id = 1
    AND translations.translatable_type = "App\\Models\\Email" 
LIMIT 1;

但用Laravel查询构建器实现相同逻辑后，id列值正确，但subject和body_html列值为空：

return Translation::selectRaw(
        'translations.id,'.
        'COALESCE(locale.subject, fallback.subject) AS subject,'.
        'COALESCE(locale.body_html, fallback.body_html) AS body_html'
    )
    ->where('translations.translatable_id', $this->id)
    ->where('translations.translatable_type', get_class($this))
    ->leftJoin('translations AS locale', function ($join) use($locale) { 
        $join->on('locale.translatable_id', 'translations.translatable_id')
            ->where('locale.translatable_type', 'translations.translatable_type')
            ->where('locale.locale', $locale);
    })
    ->leftJoin('translations AS fallback', function ($join) {
        $join->on('fallback.translatable_id', 'translations.translatable_id')
            ->where('fallback.translatable_type', 'translations.translatable_type')
            ->where('fallback.locale', config('app.fallback_locale'));
    })
    ->first();

问题原因 #

核心问题出在关联查询的条件写法上：

• 原生SQL中，translatable_type的匹配是字段与字段的相等判断（locale.translatable_type = translations.translatable_type）
• 但Laravel代码里，你用了->where('locale.translatable_type', 'translations.translatable_type')，这会把第二个参数'translations.translatable_type'当作字符串常量，而非数据库字段名。实际执行的SQL会变成locale.translatable_type = 'translations.translatable_type'，完全不符合原本的匹配逻辑，导致locale和fallback关联表无法匹配到数据，对应字段值为NULL，最终COALESCE返回空值。

解决方案 #

把关联闭包里的where改成on，明确指定字段间的相等关系：

return Translation::selectRaw(
        'translations.id,'.
        'COALESCE(locale.subject, fallback.subject) AS subject,'.
        'COALESCE(locale.body_html, fallback.body_html) AS body_html'
    )
    ->where('translations.translatable_id', $this->id)
    ->where('translations.translatable_type', get_class($this))
    ->leftJoin('translations AS locale', function ($join) use($locale) { 
        $join->on('locale.translatable_id', '=', 'translations.translatable_id')
            ->on('locale.translatable_type', '=', 'translations.translatable_type')
            ->where('locale.locale', $locale);
    })
    ->leftJoin('translations AS fallback', function ($join) {
        $join->on('fallback.translatable_id', '=', 'translations.translatable_id')
            ->on('fallback.translatable_type', '=', 'translations.translatable_type')
            ->where('fallback.locale', config('app.fallback_locale'));
    })
    ->first();

或者也可以用whereColumn方法，专门用来比较两个字段的相等性，替换对应的where行即可：

->whereColumn('locale.translatable_type', 'translations.translatable_type')

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