Open igor-rastorguev opened 3 years ago
You can select raw
$builder->selectRaw(en_translation.full_name as en_full_name,ru_translation.full_name as ru_full_name)
then you can alias the joins
$builder->leftJoin('manufacturer_translations AS en_translation', function ($j) {
$j->on('manufacturers.id', '=', 'en_translation.manufacturer_id')
->where('en_translation.locale', '=', 'en');
$builder->leftJoin('manufacturer_translations AS ru_translation', function ($j) {
$j->on('manufacturers.id', '=', 'en_translation.manufacturer_id')
->where('ru_translation.locale', '=', 'ru');
then maybe / probably (unsure about using alias here)
$builder->orderBy('en_translation.full_name');
$builder->orderBy('ru_translation.full_name');
Thanks. But i was thinking about to check, in scopeOrderByTranslation function, if table is already joined in Laravel Query Builder, then without leftJoin second time, make only orderBy.
public function scopeOrderByTranslation(Builder $query, string $translationField, string $sortMethod = 'asc')
{
$translationTable = $this->getTranslationsTable();
$localeKey = $this->getLocaleKey();
$table = $this->getTable();
$keyName = $this->getKeyName();
//!!!
$joins = collect($query->getQuery()->joins);
$flag = $joins->pluck('table')->contains($translationTable);
if ($flag) {
return $query
->orderBy("{$translationTable}.{$translationField}", $sortMethod);
} else {
return $query
->with('translations')
->select("{$table}.*")
->leftJoin ($translationTable, function (JoinClause $join) use ($translationTable, $localeKey, $table, $keyName) {
$join
->on("{$translationTable}.{$this->getTranslationRelationKey()}", '=', "{$table}.{$keyName}")
->where("{$translationTable}.{$localeKey}", $this->locale());
})
->orderBy("{$translationTable}.{$translationField}", $sortMethod);
}
}
Hey,
thanks for the working example @igor-rastorguev . I'm still unsure about it as it feels somehow hacky. 🤔 I would prefer to move the join in a dedicated scope, let this scope handle the duplicated join and let the order scope only call the new join scope. I will try to come up with a solution! 🙂
When i do multiple sort on localized columns name and full_name with orderByTranslation, I get SQL error.
SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name "manufacturer_translations" specified more than once (SQL: select "manufacturers".* from "manufacturers" left join "manufacturer_translations" on "manufacturer_translations"."manufacturer_id" = "manufacturers"."id" and "manufacturer_translations"."locale" = ru left join "manufacturer_translations" on "manufacturer_translations"."manufacturer_id" = "manufacturers"."id" and "manufacturer_translations"."locale" = ru where "manufacturers"."deleted_at" is null order by "manufacturer_translations"."name" asc, "manufacturer_translations"."full_name" asc limit 51 offset 0)
How i can avoid this problem?