Laravel查询生成器不适用于COALESCE

ct3nt3jp  于 2022-12-24  发布在  其他
关注(0)|答案(1)|浏览(111)

这个MySQL查询运行良好,返回subjectbody_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查询生成器相同,(注:此处使用的变量具有正确的值):

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();

id列值正确,但subjectbody_html列值为空。
为什么?

rta7y2nd

rta7y2nd1#

您的查询基本上是正确的,但是在两个连接中都有一个错误:

->leftJoin('translations AS locale', function ($join) use($locale) { 
    $join->on('locale.translatable_id', 'translations.translatable_id')
        ->where('locale.translatable_type', 'translations.translatable_type') // <- HERE
        ->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') // <- HERE
        ->where('fallback.locale', config('app.fallback_locale'));
})

当执行where('fallback.translatable_type', 'translations.translatable_type')时,它不是比较两列,而是试图将fallback.translatable_type与字符串字面量'translations.translatable_type'进行匹配。
只需将这些where方法替换为whereColumnon,它就可以工作。
一个二个一个一个

相关问题