Laravel MySQL – Resolving Relation Difficulties

developmentMySQLpivotrelational-theory

I have a "users" table:

enter image description here

And an organisations table ("orgs"):

enter image description here

And this is the pivot table:

enter image description here

I have some relations:

On the "users" table, I have a function called "orgs()" which is a belongsToMany relation to "orgs" (org_user,org_id,user_id).

On the "orgs" table, I have a function called "users()" which is a hasMany relation to "users" (org_user,org_id_user_id).

Now, in Laravel, when I do:

$orgUser = new OrgUser;
$orgUser->org_id=$org->id;
$orgUser->user_id=$user->id;
$orgUser->role_id=10;
$orgUser->save();

I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`eamorr`.`org_user`, CONSTRAINT `org_user_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)) (SQL: insert into `org_user` (`org_id`, `user_id`, `role_id`, `updated_at`, `created_at`) values (3, 1, -1, 2015-07-22 10:29:04, 2015-07-22 10:29:04))'

I would have thought I could just do something like:

$org->users()->attach(...)

or similar instead of having to reference the "org_user" table directly?

Does anyone have any experience at this?

Here's the User.php model:

namespace App;

use Illuminate\Auth\Authenticatable;
use Illuminate\Contracts\Auth\Authenticatable as AuthenticatableContract;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class User extends Model implements AuthenticatableContract {

    protected $table = 'users';
    public $timestamps = true;

    use Authenticatable;
    use SoftDeletes;

    protected $dates = ['deleted_at'];

    public function orgs()
    {
        return $this->belongsToMany('\App\Org', 'org_user', 'org_id', 'user_id')->withPivot('role_id');
    }

    public function files()
    {
        return $this->hasMany('\App\File', 'user_id');
    }

    public function clients()
    {
        return $this->hasMany('\App\User', 'user_user', 'owner_id', 'client_id');
    }

    public function tags()
    {
        return $this->hasMany('\App\UserTag', 'user_id');
    }

}

And here's the Org.php model:

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Org extends Model {

    protected $table = 'orgs';
    public $timestamps = true;

    use SoftDeletes;

    protected $dates = ['deleted_at'];

    public function files()
    {
        return $this->hasMany('\App\File', 'org_id');
    }

    public function locations()
    {
        return $this->hasMany('\App\Location', 'org_id');
    }

    public function tags()
    {
        return $this->hasMany('\App\OrgTag', 'org_id');
    }

    public function users()
    {
        return $this->hasMany('\App\User', 'org_user', 'org_id_user_id')->withPivot('role_id');
    }

}

And here's the OrgUser.php model:

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class OrgUser extends Model {

    protected $table = 'org_user';
    public $timestamps = true;

    use SoftDeletes;

    protected $dates = ['deleted_at'];

}

Best Answer

What you expected to have actually exists in Eloquent. In order to attach/detach related models you need to call attach/detach methods on the relations and pass a single ID or array of IDs:

$user->roles()->attach($roleId);
$user->roles()->attach([$roleId1, $roleId2]);
$role->users()->attach($userId);
$role->users()->attach([$userId1, $userId2]);

See some more info at http://laravel.com/docs/5.1/eloquent-relationships#inserting-re lated-models - the examples are pretty close to your case, as it's also about user-role many-to-many relationship.