Multiple orderByTranslation() brings sql ERROR: Duplicate alias

[igor-rastorguev]

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?

[ciamarro]

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

[igor-rastorguev]

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

[Gummibeer]

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! 🙂