Strange behavior on laravel many to many relationship

Question

I have two models User and Tenant and in my project, a User can have many Tenants connected to him and Tenant can have many users connect to him.

This is my User model

public function tenants()
{
    return $this->beLongsToMany(\App\Models\TenantsUsers::class, 'tenants_user', 'user_id', 'tenant_id');
}

This is my Tenant model

public function users()
{
    return $this->beLongsToMany(\App\Models\TenantsUsers::class, 'tenants_user', 'tenant_id', 'user_id');
}

And this is my TenantsUsers model

class TenantsUsers extends Model
{
    use UtilTrait;
    use Notifiable;

    protected $table = 'tenants_user';

    protected function serializeDate(DateTimeInterface $date)
    {
        return $date->format('Y-m-d H:i:s');
    }

    /**
     * The attributes that should be casted to native types.
     *
     * @var array
     */
    protected $casts = [
        'user_id' => 'integer',
        'tenant_id' => 'integer'
    ];

    /**
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
     **/
    public function tenants()
    {
        return $this->hasMany(\App\Models\Tenant::class, 'tenant_id');
    }

    public function users()
    {
        return $this->hasMany(\App\Models\User::class, 'user_id');
    }

When I execute this function from the repository:

$userTemp = $this->userRepository->with(['tenants'])->findWhere(['email' => $userEmail])->first();

And I'm getting this error :

SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name "typo_tenants_user" specified more than once (SQL: select
"typo_tenants_user".*, "typo_tenants_user"."user_id" as "pivot_user_id", "typo_tenants_user"."tenant_id" as
"pivot_tenant_id" from "typo_tenants_user" inner join "typo_tenants_user" on "typo_tenants_user"."id" =
"typo_tenants_user"."tenant_id" where "typo_tenants_user"."user_id" in (1))

What I'm doing wrong?

Answer 1

You don't need to create a model for pivot tables in Eloquent many-to-many relationships. Instead, use the related model's class when defining the relationship:

// User model

public function tenants()
{
    return $this->belongsToMany(\App\Models\Tenant::class, 'tenants_user', 'user_id', 'tenant_id');
}

// Tenant model

public function users()
{
    return $this->belongsToMany(\App\Models\User::class, 'tenants_user', 'tenant_id', 'user_id');
}

If you follow Eloquent naming conventions by defining the pivot table as tenant_user rather than tenants_user, things can be even further simplified to:

// User model

public function tenants()
{
    return $this->belongsToMany(\App\Models\Tenant::class);
}

// Tenant model

public function users()
{
    return $this->belongsToMany(\App\Models\User::class);
}