3 models many-to-many sync in Laravel 5.4

Question

I have 3 models with the relations many-to-many:

Module

public function permissionTypes()
{
    return $this->belongsToMany(PermissionType::class, 'permissions')->withPivot('role_id');
}

public function roles()
{
    return $this->belongsToMany(Role::class, 'permissions')->withPivot('permission_type_id');
}

Role

public function permissionTypes()
{
    return $this->belongsToMany(PermissionType::class, 'permissions')->withPivot('module_id');
}

public function modules()
{
    return $this->belongsToMany(Module::class, 'permissions')->withPivot('permission_type_id');
}

PermissionType

public function roles() 
{
    return $this->belongsToMany(Role::class, 'permissions')->withPivot('module_id');
}

public function modules()
{
    return $this->belongsToMany(Module::class, 'permissions')->withPivot('role_id');
}

tables description:

modules

id
title
status

roles

id
title

permission_types

id
title

pivot table permissions

id
role_id
module_id
permission_type_id

My synchronization looks like:

//array of ids from request to synchronization 
$permissions = $request['permissions'];

//role by id from request
$role = Role::findOrFail((int)$roleId);

//module by id from request
$module = Module::findOrFail((int)$moduleId);

//synchronization
$pivotData = array_fill(0, count($permissions), ['role_id' => $role->id]);
$syncData  = array_combine($permissions, $pivotData);
$module->permissionTypes()->sync($syncData);

When trying to make the synchronization, have an error

QueryException in Connection.php line 647: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'permissions' (SQL: select permissions.*, permissions.role_id as pivot_role_id, permissions.permission_id as pivot_permission_id from permissions inner join permissions on permissions.id = permissions.permission_id where permissions.role_id = 1)

Thanks

Answer 1

IMHO you are trying to design a triple many to many that does not exists in Laravel. The solution, usually is to give the pivot table (in your case permissions) a Model (Permission in below code) for defining hasManyThrough relations.

If I understood well your table structure I will design the following relationships:

Module

public function permissions()
{
    return $this->hasMany(Permission::class);
}
public function roles()
{
    return $this->hasManyThrough(Role::class, Permission::class);
}
public function permissionTypes()
{
    return $this->hasManyThrough(PermissionType::class, Permission::class);
}

Role

public function permissions()
{
    return $this->hasMany(Permission::class);
}
public function permissionTypes()
{
   return $this->hasManyThrough(PermissionType::class, Permission::class);
}   
public function modules()
{
    return $this->hasManyThrough(Module::class, Permission::class);
}

PermissionType

public function permissions()
{
    return $this->hasMany(Permission::class);
}
public function modules()
{
    return $this->hasManyThrough(Module::class, Permission::class);
}
public function roles()
{
   return $this->hasManyThrough(Role::class, Permission::class);
}

Permission

public function permissionType()
{
    return $this->belongsTo(PermissionType::class);
}
public function role()
{
    return $this->belongsTo(Role::class);
}
public function module()
{
    return $this->belongsTo(Module::class);
}

Tell me if it could work for you.