2

Is it allowed to have two tables, each with a belongsToMany() relation to each other, using the same pivot table?

Here is my User table:

enter image description here

And here is my Org table:

enter image description here

And here is the pivot table:

enter image description here

Here is the Eloquent User.php model:

<?php

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 {

    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');
    }
}

And here is the Eloquent Org.php model:

<?php

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 users()
    {
        return $this->belongsToMany('App\User', 'org_user', 'org_id, user_id')->withPivot('role_id');
    }

}

Is it OK to do this? (i.e. use a shared pivot table) Does anyone with experience foresee any issues here? Or maybe someone could comment on standard practice/share some insights?

4
  • 2
    This looks like a programming question, however. Firstly, can you enable the general log on MySQL so that we can see the actual query that's going to the server, rather than PHP code? Second, can you please repost your tables structures as (formatted) text and not as graphics (it's impossible to cut and paste a graphic into the mysql client).
    – Vérace
    Jul 25, 2015 at 15:19
  • Hi Vérace, So I have a User->belongsToMany()->Org relation and a Org->belongsToMany()->User relation. I'm using the same pivot table (as above) for both relations. I have the pair org_id/user_id set to unique. Could you please comment on the shared pivot?
    – Eamorr
    Jul 25, 2015 at 15:33
  • 1
    It's very difficult to visualise. Could you run SHOW CREATE TABLE My_Table\G for all tables and post the resultant text (formatted as code) with your question. Then comment here using @Vérace.
    – Vérace
    Jul 25, 2015 at 16:56
  • 3
    Voted to transfer this to SO. I doubt there are people in this site that have experience with php/eloquent. Jul 25, 2015 at 17:31

1 Answer 1

2

Absolutely you can! This is the beauty of a many to many relationship, in fact the example at http://laravel.com/docs/4.2/eloquent#many-to-many has the User and Role classes both set with belongsToMany. Thought being in that case (and sounds like in yours as well) that a user can have multiple roles and each role can be associated with multiple users.

Regarding your last two questions, the only part that gave me pause is the use of a role_id within the pivot table. The code you provided doesn't go into detail on what purpose that serves. As long as the User would only ever have one role associated with an Org this is fine, but if it could ever be more than one I would recommend another table to hold that User to Org role relationship.

1
  • This is a very useful confirmation. Your suggestion is also very useful. Thank you.
    – Eamorr
    Jul 26, 2015 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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