MySQL/Eloquent belongsToMany relation - two tables same pivot table allowed?

Question

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:

And here is my Org table:

And here is the pivot table:

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?

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
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
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
Voted to transfer this to SO. I doubt there are people in this site that have experience with php/eloquent. — ypercubeᵀᴹ

stevenwoodson stevenwoodson · Accepted Answer · 2015-07-26T03:32:00

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.

MySQL/Eloquent belongsToMany relation - two tables same pivot table allowed?

1 Answers