Laravel:使用雄辩连接只限制来自第二个表的1条记录

Question

我有两个表colors和color_translations的结构如下:

<表类> id created_at updated_at tbody><<tr>12021-08-25空22021-09-01空

我不知道多少laravel，但查询应该是这样的:

(使用@Bill Karwin在这里描述的最佳性能技术)

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;

注意:这样以后如果您添加任何新的区域设置，该区域设置将具有最高优先级，因为FIELD()对于未指定的值返回0。我建议您确保每次在应用程序中运行此查询时。

---

我试图用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();

您可以在order raw查询中使用case。它将从选项中选择任何一个。

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

如果您想在不影响性能的情况下使用SQL/Eloquent完成此操作，您可以通过查询所有翻译来使用order:

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

这将获得第一个相关的翻译，按您自定义的语言环境列表顺序排序，因此语言环境回退。假设你有合适的模型，并且在你的模型和翻译的模型之间有一个translations关系(color hasMany color_translations)。

array_walk是将orderByRaw和FIELD的值用双引号括起来。

您可以将其包装在Color模型上的作用域函数中，以便轻松查询:

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

则命名为:Color::withFallbackTranslation()->where(stuff)->get()

注意->translations仍然是一个集合，因此需要一个->first()(和空复选)。例如，您可以使用Color模型上的自定义属性获得单个对象。