Laravel使用Eloquent join时如何从关联表按locale优先级仅取1条记录

问题原因 #

你原有代码的错误在于：leftJoin闭包内添加的limit(1)是对整个color_translations关联表做全局行数限制，而非按每个color_id分组后分别取优先级最高的1条翻译记录，关联时仍会匹配所有colors.id = color_translations.color_id的行，因此返回5条结果。

解决方案 #

推荐使用MySQL 8.0及以上支持的窗口函数ROW_NUMBER()实现分组取top1的需求，对应的Eloquent代码如下：

use Illuminate\Support\Facades\DB;

$this->colorsModel
    ->select([
        'colors.*',
        'ct.locale',
        'ct.title',
        'ct.url'
    ])
    ->leftJoinSub(
        // 子查询给每个color对应的翻译按优先级排序打编号
        DB::table('color_translations')
            ->selectRaw('*, ROW_NUMBER() OVER (PARTITION BY color_id ORDER BY FIELD(locale, "en", "fr", "de") ASC) as rn')
            ->having('rn', '=', 1),
        'ct',
        'colors.id',
        '=',
        'ct.color_id'
    )
    ->get();

兼容低版本MySQL的替代方案 #

如果你的MySQL版本不支持窗口函数，可以用相关子查询的写法：

use Illuminate\Support\Facades\DB;

$this->colorsModel
    ->select([
        'colors.*',
        'color_translations.locale',
        'color_translations.title',
        'color_translations.url'
    ])
    ->leftJoin('color_translations', 'colors.id', '=', 'color_translations.color_id')
    ->whereRaw(
        'color_translations.id = (
            SELECT id FROM color_translations 
            WHERE color_id = colors.id 
            ORDER BY FIELD(locale, "en", "fr", "de") ASC 
            LIMIT 1
        )'
    )
    ->get();

内容的提问来源于stack exchange，提问作者jojo