这个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查询生成器相同,(注:此处使用的变量具有正确的值):
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列值正确,但subject
和body_html
列值为空。
为什么?
1条答案
按热度按时间rta7y2nd1#
您的查询基本上是正确的,但是在两个连接中都有一个错误:
当执行
where('fallback.translatable_type', 'translations.translatable_type')
时,它不是比较两列,而是试图将fallback.translatable_type
与字符串字面量'translations.translatable_type'
进行匹配。只需将这些
where
方法替换为whereColumn
或on
,它就可以工作。一个二个一个一个