Many to many Laravel Eloquent relationship with multiple intermediate tables

Question

I'm using Laravel 5.4 and have a model and table structure as follows:

users
-----
id

accounts
--------
id

holdings
--------
id
account_id (foreign key to account)

user_accounts
-------------
id
user_id
account_id

A user can have multiple accounts
An account can be shared by multiple users
Each account has multiple holdings
A user therefore indirectly has many holdings through their many accounts.

I need help to define a relation on the User model called "holdings" to get me all the holdings applicable to the user (based on the accounts they are linked to).

I've tried lots of different things and spent ages on google. I can get close with both belongsToMany and hasManyThrough, but they only seem to work for 3 table relationships where the intermediate table stores primary keys from the other tables. I can reduce my relationship to 3 tables (rather than 4) if I make use of the account_id foreign key on the holdings table to remove the need to join through the accounts table, however I can't seem to get this to work.

belongsToMany - holdings.id needs to be holdings.account_id:

select * from `holdings`
inner join `user_accounts` on `holdings`.`id` = `user_accounts`.`account_id`
where `user_accounts`.`user_id` = ?

hasManyThrough - user_accounts.id needs to be user_accounts.account_id:

select * from `holdings`
inner join `user_accounts` on `user_accounts`.`id` = `holdings`.`account_id`
where `user_accounts`.`user_id` = ?"

madz madz · Accepted Answer · 2018-04-27T03:50:36

Well it's not strictly an answer as I was asking about laravel 5.4, but it is a possible solution.

It turns out I'm in luck as I came across this Laravel belongsToMany relationship defining local key on both tables which indicates the belongsToMany relationship has been enhanced in Laravel 5.5 to support this type of scenario.

I have upgraded my project to L5.5 and replaced my hack with the relation:

return $this->belongsToMany(Holding::class, 'user_accounts', 'user_id', 'account_id', null, 'account_id');

And am happy to say it seems to work perfectly - at least for basic gets which is my use case, I haven't tried any updates etc.

Thanks to @cyberfly and those who posted the answer above

Many to many Laravel Eloquent relationship with multiple intermediate tables

1 Answers