Laravel 4 BelongsToMany sync() method doesn't work properly

Question

I have problem when using sync() method. Please help me out here.

I have three tables as below.

Table: tbl_roles

+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| id_role        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name           | varchar(60)      | NO   | UNI | NULL    |                |
| description    | text             | YES  |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+

Table: tbl_permissions

+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| id_permission  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name           | varchar(255)     | NO   |     | NULL    |                |
| description    | text             | YES  |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+

Table: tbl_link_roles_permissions

+---------------+------------------+------+-----+---------+-------+
| Field         | Type             | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+-------+
| id_role       | int(10) unsigned | NO   | MUL | NULL    |       |
| id_permission | int(10) unsigned | NO   | MUL | NULL    |       |
+---------------+------------------+------+-----+---------+-------+

I have created many to many relationship between roles and permissions models.

Role model:

class Role extends Eloquent
{
    /**
     * Permissions
     */
    public function permissions()
    {
        return $this->belongsToMany('Permission', 'tbl_link_roles_permissions', 'id_role', 'id_role');
    }
}

Permission model:

class Permission extends Eloquent
{
    /**
     * Roles
     */
    public function roles()
    {
        return $this->belongsToMany('Role', 'tbl_link_roles_permissions', 'id_permission', 'id_permission');
    }
}

When I run the following code to sync roles' permissions. It give me an Integrity constraint violation error.

The Code:

    $role = Role::find($id_role);

    $permissions = Input::get('role_permissions');

    /* 
     * Permissions array is like array('1','2','3')
     * So I convert the value to integer.
     */
    $parameters = array();

    foreach($permissions as $permission) {
        $parameters[] = intval($permission);
    }

    $role->permissions()->sync($parameters);

According to the error message, I found the SQL query is not correct.

Error meesage:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: 
a foreign key constraint fails (`quidz_host_laravel`.`tbl_link_roles_permissions`, 
CONSTRAINT `tbl_link_roles_permissions_id_permission_foreign` FOREIGN KEY 
(`id_permission`) REFERENCES `tbl_permissions` (`id_permission`)) (SQL: insert into 
`tbl_link_roles_permissions` (`id_role`) values (?)) (Bindings: array ( 0 => 1, ))

It only inserts id_role. This is not what I want. Can anyone tell me what I did wrong?

Thanks a lot.

Answer 1

I have solved my problem. It's caused by passing wrong parameters when building the relationships.

The relationship should be like the code below:

class Role extends Eloquent
{
    /**
     * Permissions
     */
    public function permissions()
    {
        return $this->belongsToMany('Permission', 'tbl_link_roles_permissions', 'id_role', 'id_permission');
    }
}

class Permission extends Eloquent
{
    /**
     * Roles
     */
    public function roles()
    {
        return $this->belongsToMany('Role', 'tbl_link_roles_permissions', 'id_permission', 'id_role');
    }
}