Syntax error or access Violation: 1066 Not Unique table/alias with polymorphic relationship in Laravel

Asked

Viewed 172 times

1

I’ve been racking my brain for weeks and I can’t solve this problem.

I’m using this package https://github.com/rappasoft/laravel-livewire-tables to use Datables in Laravel with Livewire.
I have models with the following relationships:

User.php

public function getAdminAttribute()
    {
        return $this->admins->first();
    }

    public function admins()
    {
        return $this->morphedByMany(Admin::class, 'userable');
    }

    public function getCoachAttribute() //permite pegar o $user->coach direto (encapsulamento)
    {
        return $this->coaches->first();
    }

    public function coaches()
    {
        return $this->morphedByMany(Coach::class, 'userable');
    }

    public function getAthleteAttribute()
    {
        return $this->athletes->first();
    }

    public function athletes()
    {
        return $this->morphedByMany(Athlete::class, 'userable');
    }

Athlete.php

public function coach()
    {
        return $this->belongsTo(Coach::class);
    }

    public function getUserAttribute()
    {
        return $this->users->first();
    }

    public function users()
    {
        return $this->morphToMany(User::class, 'userable');
    }

Coach.php

public function athletes()
    {
        return $this->hasMany(Athlete::class);
    }

    public function getUserAttribute()
    {
        return $this->users->first();
    }

    public function users()
    {
        return $this->morphToMany(User::class, 'userable');
    }

And the pivot table:

public function up()
    {
        Schema::create('userables', function (Blueprint $table) {
            $table->integer('user_id');
            $table->morphs('userable'); //nullableMorphs
        });
    }

In the package’s Livewire Component table:

public function query() : Builder
    {
        return Athlete::with(['coach.users:id,name', 'users:id,name'])->where('company_id', $this->companyId);
    }

    public function columns() : array
    {
        return [
            Column::make('Avatar', 'avatar')
                ->format(function(Athlete $model) {
                    return $this->html('<img class="rounded-circle m-0 avatar-sm-table" src="'. asset("images/users/{$model->user->id}/{$model->user->avatar}") .'" alt="avatar">');
                })
                ->excludeFromExport(),
            Column::make('Nome', 'users.name')
                ->searchable()
                ->sortable(/* aqui é o problema */)
                ->format(function(Athlete $model) {
                    return $this->html($model->user->name);
                }),
            Column::make('Treinador', 'coach.users.name')
                ->searchable()
                //->sortable()
                ->format(function(Athlete $model) {
                    return $this->html($model->coach->user->name);
                }),
                Column::make('Ações', 'actions')
                ->format(function(Athlete $model) {
                    return $this->html(
                        '<a href="'. route('athletes.profile', ['uuid' => $model->uuid]) .'" class="btn btn-outline-info btn-icon m-0 m-0" data-toggle="tooltip" data-trigger="hover" data-original-title="Ver atleta" title="Ver atleta" target="_blank">
                            <i class="nav-icon fas fa-eye font-weight-bold"></i>
                        </a>
                        <a href="#" wire:click.prevent="showConfirmation('. $model->uuid .')" class="btn btn-outline-danger btn-icon m-0 m-0 alert-confirm" data-toggle="tooltip" data-trigger="hover" data-original-title="Desvincular atleta da empresa" title="Desvincular atleta da empresa">
                            <i class="nav-icon fas fa-user-times font-weight-bold"></i>
                        </a>'
                    );
                })
                ->excludeFromExport(),
        ];
    }

The error returned is this:

Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'users' (SQL: select count(*) as aggregate from `athletes` left join `userables` on `userables`.`userable_id` = `athletes`.`id` left join `users` on `userables`.`user_id` = `users`.`id` left join `users` on `userables`.`user_id` = `users`.`id` where `company_id` = 1)

There should be a way to create a Alias?, but it turns out that the package does this automatically (these joins) and I didn’t understand how to get around. I’ve tried lots of things and not for sure,

Even in the passage commented there aqui é o problema, in git of the package, they tell you when you need to work around something different in sortable(), to do it inside using Builder. In case I tried to do that:

Column::make('Nome', 'users.name')
                ->searchable()
                ->sortable(function ($builder, $direction) {
                    return $builder->join('userables', 'athletes.id', '=', 'userables.userable_id')
                                ->join('users', 'users.id', '=', 'userables.user_id')
                                ->where('userables.userable_type', '=', Athlete::class)
                                ->orderBy('users.name', $direction);
                } )
                ->format(function(Athlete $model) {
                    return $this->html($model->user->name);
                })

So no error happens, but does not order correctly by names.

Can someone help me with that?

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.