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

color_translations

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

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

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