Laravel belongsToMany relationship defining local key on both tables

14
votes

So the belongsToMany relationship is a many-to-many relationship so a pivot table is required

Example we have a users table and a roles table and a user_roles pivot table.

The pivot table has two columns, user_id, foo_id... foo_id referring to the id in roles table.

So to do this we write the following in the user eloquent model:

return $this->belongsToMany('Role', 'user_roles', 'user_id', 'foo_id');

Now this looks for an id field in users table and joins it with the user_id field in the user_roles table.

Issue is I want to specify a different field, other than id to join on in the users table. For example I have bar_id in the users table that I want to use as the local key to join with user_id

From laravel's documentation, it is not clear on how to do this. In other relationships like hasMany and belongsTo we can specify local key and foriegn key but not in here for some reason.

I want the local key on the users table to be bar_id instead of just id.

How can I do this?

6
phpsqldatabaselaravellaravel-4

6 Answers

24
votes

Update: as of Laravel 5.5 onwards it is possible with generic relation method, as mentioned by @cyberfly below:

public function categories()
{
    return $this->belongsToMany(
         Category::class,
         'service_categories',
         'service_id',
         'category_id', 
         'uuid',  // new in 5.5
         'uuid'   // new in 5.5
    );
}

for reference, previous method:

I assume id is the primary key on your User model, so there is no way to do this with Eloquent methods, because belongsToMany uses $model->getKey() to get that key.

So you need to create custom relation extending belongsToMany that will do what you need.

A quick guess you could try: (not tested, but won't work with eager loading for sure)

// User model
protected function setPrimaryKey($key)
{
  $this->primaryKey = $key;
}

public function roles()
{
  $this->setPrimaryKey('desiredUserColumn');

  $relation = $this->belongsToMany('Role', 'user_roles', 'user_id', 'foo_id');

  $this->setPrimaryKey('id');

  return $relation;
}
7
votes

On Laravel 5.5 and above, 

    public function categories()
    {
        return $this->belongsToMany(Category::class,'service_categories','service_id','category_id', 'uuid', 'uuid');
    }

From the source code:

    public function belongsToMany($related, $table = null, $foreignPivotKey = null, $relatedPivotKey = null,
                                  $parentKey = null, $relatedKey = null, $relation = null)
    {}
3
votes

This is a recently added feature. I had to upgrade to 4.1 because I was also looking for this.

From the API documentation:

public BelongsToMany belongsToMany(string $related, string $table = null, string $foreignKey = null, string $otherKey = null, string $relation = null)

The $otherKey and $relation parameters were added in 4.1. Using the $foreignKey and $otherKey parameters allows you to specify the keys on both sides of the relation.

2
votes

The best way is set the primary key.

class Table extends Eloquent {

     protected $table = 'table_name';

     protected $primaryKey = 'local_key';
0
votes

belongsToMany allows to define the name of the fields that are going to store che keys in the pivot table but the method insert always the primary key values into these fields.

You have to:

  • define in the method belongsToMany the table and the columns;

  • then using protected $primaryKey = 'local_key'; you can choose which value store.

0
votes

I recently went through the same problem where I needed to have an associated table that used ID's to link two tables together that were not Primary Keys. Basically what I did was create a copy of my model that models the pivot table and set the Primary Key to the value that I wanted it to use. I tried creating a model instance, settings the primary key and then passing that to the relation but Laravel was not respecting the primary key I had set ( using the ->setPrimaryKey() method above ).

Making a copy of the model and setting the primary key feels a little bit 'hackish' but in the end it works as it should and since Pivot table models are generally very small I don't see it causing any problems in the future.

Would love to see a third key option available in the next release of Laravel that lets you get more specific with your linking.