CODEWITHSUNDEEP
mysqllaraveljoingroupwise-maximumlateral-join
jojo
jojo

Reputation: 134

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

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

Upvotes: 2

Views: 2673

Answers (3)

Mtxz
Mtxz

Reputation: 3869

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.

Upvotes: 0

Soheil Rahsaz
Soheil Rahsaz

Reputation: 791

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

Upvotes: 1

Pravina Upadhyay
Pravina Upadhyay

Reputation: 29

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

Upvotes: 0

Related Questions