2

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'];

}
2

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.

10
  • Hello, thank you for your very useful answer. I hope to try this out and have a more detailed look in about 20 minutes.
    – Eamorr
    Jul 22 '15 at 12:54
  • 1
    This won't set org_id at all. In order to set org_id you'd need to do $user->roles()->attach($roleId, ['org_id' => $orgId]); This will link user to role and set org_id to $orgId. If you want to enforce different roles per user for each organization you'll need to add unique constraints for (user_id, role_id, org_id) in the database. It would be useful to check for that before data is sent to the database doing the validation with laravel. Jul 22 '15 at 13:32
  • 1
    Ah, sorry, didn't notice. Then you need to change the logic a bit. $user->orgs()->attach($orgId, ['role_id' => $roleId]); The second argument is the additional pivot data. Moreover, I think it should be possible to define both orgs() and roles() relations in your User model, the first declaring role_id as additional, the second declaring org_id. Just make sure both relations use the same table. Then you should be able to do both $user->roles()->attach($roleId, ['org_id'=>$orgId]) and $user->orgs()->attach($orgId, ['role_id' => $role_id]); Jul 22 '15 at 13:48
  • 1
    You shouldn't need to pass user_id. It will be taken from the $user you're using. Jul 22 '15 at 14:06
  • 1
    In that case I suggest you use "'user_id' => $user->id " instaed of "'user_id' => $userID'" to make sure that used user_id belongs to the user you're linking orgs to Jul 22 '15 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.