Search code examples
laravelbuilder

Laravel query builder doesn't work with COALESCE

This MySQL query works perfectly and return values in both subject and body_html columns.

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;

Now the same with the Laravel query builder, (note: the variables used here have a correct value):

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

The id column value is correct but subjectand body_html column values are empty.
Why ?

Solution

  • Your query is mostly correct, but there's a mistake in both of your joins:

    ->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'));
})

    When you do where('fallback.translatable_type', 'translations.translatable_type'), it's not comparing the 2 columns. It's trying to match fallback.translatable_type to the string literal 'translations.translatable_type'.

    Simply replace those where methods by either whereColumn or on and it should work.

    DB::table('translations')->selectRaw(
        'translations.id,'.
        'COALESCE(locale.subject, fallback.subject) AS subject,'.
        'COALESCE(locale.body_html, fallback.body_html) AS body_html'
    )
    ->where('translations.translatable_id', 1)
    ->where('translations.translatable_type', 'App\\Models\\Translation')
    ->leftJoin('translations AS locale', function ($join) { 
        $join->on('locale.translatable_id', 'translations.translatable_id')
            ->whereColumn('locale.translatable_type', 'translations.translatable_type')
            ->where('locale.locale', 'fr');
    })
    ->leftJoin('translations AS fallback', function ($join) {
        $join->on('fallback.translatable_id', 'translations.translatable_id')
            ->whereColumn('fallback.translatable_type', 'translations.translatable_type')
            ->where('fallback.locale', config('app.fallback_locale'));
    })
    ->first();

    DB::table('translations')->selectRaw(
        'translations.id,'.
        'COALESCE(locale.subject, fallback.subject) AS subject,'.
        'COALESCE(locale.body_html, fallback.body_html) AS body_html'
    )
    ->where('translations.translatable_id', 1)
    ->where('translations.translatable_type', 'App\\Models\\Translation')
    ->leftJoin('translations AS locale', function ($join) { 
        $join->on('locale.translatable_id', 'translations.translatable_id')
            ->on('locale.translatable_type', 'translations.translatable_type')
            ->where('locale.locale', 'fr');
    })
    ->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();