Framework: belongsToMany relation with non-primary keys

[gandalfx]

In my database layout exists a many-to-many relationship where the related keys in the Models are not the primary keys. Laravel's belongsToMany relation currently only allows you to specify the foreign keys on the pivot table, but not the corresponding keys on the relations:

// Example from the documentation for Laravel 4.2
$this->belongsToMany('Role', 'user_roles', 'user_id', 'foo_id');

I suggest two additional optional parameters:

// signature
function belongsToMany(
    $related,
    $table = null,
    $foreignKey = null,
    $otherKey = null,
    $foreignTableKey = null,
    $otherTableKey = null
)
// generic example
$this->belongsToMany(
    'Model2',
    'model1s_model2s',
    'model1_nonprimary_id',
    'model2_nonprimary_id',
    'nonprimary_id',
    'nonprimary_id'
)

This amounts to a total of six parameters (five optional). Since there are a total of three different tables to consider I think that is justified.
edit: It also brings more consistency to the API as the equivalent is already possible in the belongsTo relation via the third parameter:

// Example from the documentation for Laravel 4.2
return $this->belongsTo('User', 'local_key', 'parent_key');

In my case the reason to use this is, that one of the related tables uses a (non unique) grouped id that the pivot table's foreign key points to. I'm sure there are many other reasons why database layouts may need to use non-primary keys for many-to-many relations. For example one Modelmmay be a sort of side feature that only relates to some instances of a main model.

In case there are other ways to achieve this, please let me know. So far I only found this on stackoverflow which didn't work.

Thanks and cheers.

[hernandev]

wherePivot would not work for that? the way you propose is not really syntactically intuitive for users, the normal 4 parameters already makes lot of confusion on people new to Laravel

Manual Suggestions

1) Make a Model that represents the pivot table, like UserRole with user and role relationships on both sides (this have the downside of not allowing you to use sync and some other methods)

2) Extend the two base methods overriding the original primary key with the key you want, that will require some more work when dealing with your data, but is perfectly usable on Class constraints.

(those two are the ones i can think of right now, bu sure there are other workarounds).

Adding this as a feature would not have too much impact on users since is really not usual setup for database, not sure if supporting it worse the work.

I'm not against it, just putting my point of view

[gandalfx]

Thanks for your feedback.

1) is kind of what we are using now. The problem is, that this brings along unneeded data and ugly code, since the actual model has to be manually retrieved from the pivot model. I'm not saying it can't be done, but what is the point of using ORM if it can't give you the simplification it is promising?

I'm not quite sure how your second suggestion would work. As far as I can tell I'd have to create a new class for that relation, extending the BelongsToMany class. That seems to be a rather 'implement the whole thing myself' kind of approach.

Adding those two extra parameters may not be beautiful but they will, after all, be optional, which is why I queued them at the end in my proposition. Once you have managed to get your head around all the other parameters and related functions, those two shouldn't be the ones that get you.

And supporting 'unusual setups' is where a framework shows it's superiority, isn't it? ;D
Also as I pointed out above, this was already done for all the other relation types, since all other relation methods already offer the equivalent parameter in the third place respectively. I was actually rather surprised when I found out that belongsToMany was an exception to that concept.

[rhynodesigns]

@gandalfx love the suggestion!

We just ran into this problem (again), due to legacy DB architecture beyond our control.

We had to resort to do what's suggested here:

http://stackoverflow.com/questions/24391759/laravel-belongstomany-relationship-defining-local-key-on-both-tables

[gandalfx]

@rhynodesigns I tried that, too. It didn't work, although I'm starting to think I might have been doing it wrong. Though that doesn't mean it should be necessary to resort to hacks like that.

[rhynodesigns]

@gandalfx exactly!

we were all "uncomfortable" with the hack, but we'll use it until your suggested nonprimary_id makes an appearance.

[gandalfx]

Just an addition to my last post: I wasn't doing it wrong, the method simply only works in one direction.
If model B uses its primary key for the relation as expected by the Relation builder, I can fetch its instances via model A using the trick mentioned above. However it is not possible the other way round since I can't set the foreign models primary key that way.

[GrahamCampbell]

We're open to pull requests.

[pechatny]

Try something like this:

$this->primaryKey = 'nonprimary_id'; 
$this->belongsToMany(
    'Model2',
    'model1s_model2s',
    'model1_nonprimary_id',
    'model2_nonprimary_id'
)

[gfd6th]

@pechatny That works, So what's the solution for the inverse many to many relationship?

[luryson]

@gfd6th Hi, I have encountered the same problem. What's your solution for it?

[gfd6th]

@luryson I created another model to the same table by protected $table property.

[jeffz2012]

This:
https://stackoverflow.com/a/47544298/436443
suggest @gandalfx solution incorporated in L5.
But I cannot find it in my L5.4
Found it ... L5.5