Laravel: Limit only 1 record from the 2nd table using eloquent join

Question

I have a two tables colors and color_translations with structure like this:

colors

id	created_at	updated_at
1	2021-08-25	NULL
2	2021-09-01	NULL

color_translations

id	color_id	locale	title	url
1	1	en	blue	blue-cat
2	1	fr	bleu	bleu-cat
3	1	de	blau	blau-cat
4	2	de	rot	rot-cat
5	2	fr	rouge	rouge-cat

I want to merge the colors table with only one of the record from color_translations table, the record will be based on the locale column, it will look at the en locale records first, if not exists then take from fr locale , then de locale

id	created_at	updated_at	locale	title	url
1	2021-08-25	NULL	en	blue	blue-cat
2	2021-09-01	NULL	fr	rouge	rouge-cat

i tried to do it like this using my eloquent model:

 $this->colorsModel
    ->select( 
        [
            'colors.*',
            'color_translations.locale as locale', 
            'color_translations.title as title',
            'color_translations.url as url'
        ]   
    )
    ->leftJoin ('color_translations', function ($query) {
        $query->on('colors.id', '=', 'color_translations.color_id')
         ->orderByRaw('FIELD(color_translations.locale, "en", "fr", "de)')
         ->limit(1);
    })->get();

using above code instead of having 2 records I'm getting all 5 records from the color_translations table

Try adding group by on column locale. – atikur rahman Sep 09 '21 at 15:22 — atikur rahman, Sep 09 '21 at 15:22

Soheil Rahsaz · Accepted Answer · 2021-09-11T09:20:26.747

I don't know much laravel but the query should look like this:

(Using technique described by @Bill Karwin here with the best performance)

SELECT t1.*, t2.locale, t2.title, t2.url
FROM colors t1
         LEFT OUTER JOIN color_translation t2 ON t1.id = t2.color_id
         LEFT OUTER JOIN color_translation t3 ON t2.color_id = t3.color_id
                             AND FIELD(t2.locale, 'en', 'fr', 'de') > FIELD(t3.locale, 'en', 'fr', 'de')
WHERE t3.id IS NULL;

Note This way in the future if you add any new locales, that locale will be the highest priority as FIELD() returns 0 for values that are not specified. I suggest that you make sure every time you run this query in your application.

My effort trying to write it in laravel:

$this->colorsModel
    ->select( 
        [
            'colors.*',
            't2.locale as locale', 
            't2.title as title',
            't2.url as url'
        ]   
    )
    ->leftJoin ('color_translations AS t2', function ($query) {
        $query->on('colors.id', '=', 't2.color_id')
    })->leftJoin ('color_translations AS t3', function ($query) {
        $query->on('t2.color_id', '=', 't3.color_id');
        $query->on(DB::raw('FIELD(t2.locale, \'en\', \'fr\', \'de\')'), '>', DB::raw('FIELD(t3.locale, \'en\', \'fr\', \'de\')'));
    })->whereNull('t3.id')->get();

(`LATERAL` is not available until 8.0.14.) – Rick James Sep 07 '21 at 06:25 — Rick James, Sep 07 '21 at 06:25
is there a way to put this using the eloquent model ? – jojo Sep 07 '21 at 07:24 — jojo, Sep 07 '21 at 07:24
Groupwise max: http://mysql.rjweb.org/doc.php/groupwise_max – Rick James Sep 09 '21 at 03:12 — Rick James, Sep 09 '21 at 03:12

score 0 · Answer 2 · answered Sep 01 '21 at 05:19

You can use the case in orderby raw query. That will pick any one from the options.

$this->colorsModel
                    ->select(
                        [
                            'colors.*',
                            'color_translations.locale as locale',
                            'color_translations.title as title',
                            'color_translations.url as url'
                        ]
                    )
                    ->leftJoin ('color_translations', function ($query) {
                        $query->on('colors.id', '=', 'color_translations.color_id')
                            ->orderByRaw('FIELD(color_translations.locale, CASE  WHEN (color_translations.locale  = "en") THEN en 
                            WHEN (color_translations.locale  = "fr")  THEN fr 
                            ELSE de )')
                            ->limit(1);
                    })->get();

Not working, i'm still getting the same result – jojo Sep 01 '21 at 05:59 — jojo, Sep 01 '21 at 05:59

score 0 · Answer 3 · edited Nov 04 '21 at 20:31

If you want to do it with SQL/Eloquent, without impacting performance, you can indeed use a orderBy by querying all the translations:

$locales_ordered = ['en', 'fr', 'nl'];
array_walk($locales_ordered, function(&$x) {$x = "'$x'";});
$query = Colors::with([ 'translations' => function ($q) use ($locales_ordered) {
     $q->orderByRaw('FIELD(colors_translations.locale,' . implode(',', $locales_ordered) . ') ASC')->first();
        } ])
  ->first();
$translation = $query->first()->translations->first();

This will get the first associated translation, ordered by your custom locale list order, and so locale fallback. Assuming you have proper Models and a translations relationship (color hasMany color_translations) between your Model and the translated Model.

The array_walk is to put double-quotes around values for the orderByRaw with FIELD using strings.

You can wrap it in a scoped function on your Color model to query it easily:

public function scopeWithFallbackTranslation($query)
{
    $locales_ordered = ['en', 'fr', 'nl']; //todo get from helper class, headers, ssession etc..
    array_walk($locales_ordered, function(&$x) {$x = "'$x'";});
    return $query
        ->with([ 'translations' => function ($q) use ($locales_ordered) {
            $q
                ->orderByRaw('FIELD(email_notification_senders_translations.locale,' . implode(',', $locales_ordered) . ') ASC')
                ->first();
        } ]);
}

Then call it: Color::withFallbackTranslation()->where(stuff)->get()

Note that the ->translations will still be a Collection, and so need a ->first() (and empty check). You could get a single object using a custom attribute on the Color model for example.

Laravel: Limit only 1 record from the 2nd table using eloquent join

3 Answers3