Laravel关联查询：按优先语言返回帖子翻译标题

问题分析 #

当前查询通过join关联评论、帖子和帖子翻译表，但groupBy后默认取到的是英文翻译，无法根据当前语言（如nl）优先返回对应语言的标题。之前尝试的orderByRaw失效是因为SQL执行顺序中GROUP BY先于ORDER BY执行，分组完成后排序无法改变已选中的翻译数据。

解决方案1：使用条件聚合函数优先选择目标语言 #

利用COALESCE和CASE语句，先尝试获取当前语言的翻译，若不存在则回退到英文翻译，通过聚合函数配合GROUP BY实现需求：

$currentLocale = app()->getLocale();

$comments = Comment::select([
    'comments.post_id',
    DB::raw("COALESCE(
        MAX(CASE WHEN posts_translations.locale = ? THEN posts_translations.title END),
        MAX(CASE WHEN posts_translations.locale = 'en' THEN posts_translations.title END)
    ) AS post_title", [$currentLocale])
])
->join('posts', 'comments.post_id', '=', 'posts.id')
->join('posts_translations', 'posts.id', '=', 'posts_translations.post_id')
->groupBy('comments.id', 'comments.post_id')
->get();

代码说明 #

• COALESCE函数返回第一个非空值，优先匹配当前语言的翻译，为空则取英文翻译
• 使用参数绑定传递$currentLocale避免SQL注入风险
• MAX仅适配GROUP BY语法，实际只会返回对应语言的唯一值

解决方案2：子查询排序后关联（确保分组前已按优先级排序） #

通过子查询先对帖子翻译按语言优先级排序，再关联到主查询，GROUP BY时会取到排序后的第一条数据（即优先目标语言）：

$currentLocale = app()->getLocale();

$comments = Comment::select('comments.post_id', 'pt.title AS post_title')
->join('posts', 'comments.post_id', '=', 'posts.id')
->joinSub(
    // 子查询：按语言优先级排序，当前语言排最前，英文次之，其他最后
    PostTranslation::select('post_id', 'title', 'locale')
        ->orderByRaw("CASE WHEN locale = ? THEN 0 WHEN locale = 'en' THEN 1 ELSE 2 END", [$currentLocale]),
    'pt', // 子查询别名
    'posts.id',
    '=',
    'pt.post_id'
)
->groupBy('comments.id', 'comments.post_id')
->get();

代码说明 #

• 子查询通过orderByRaw定义排序规则，确保当前语言的翻译排在最前面
• 主查询关联排序后的子查询结果，GROUP BY默认取每组第一条数据，即优先级最高的翻译

注意事项 #

• 确保posts_translations表中每个帖子至少存在英文翻译，避免出现标题为空的情况
• 若项目使用Laravel的多语言扩展包（如spatie/laravel-translatable），可直接利用包内置方法简化查询逻辑

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