Laravel 3 way intermediate pivot table

Question

I am having trouble defining the relationships in my Eloquent Models when using a 3 way intermediate pivot table.

Here are the 4 tables in my database:

users
    - id
    - name

instruments
    - id
    - key

instruments_users
    - id
    - user_id
    - instrument_id
    - level_id

levels
    - id
    - key

Instruments keys can be: guitare, piano, trumpet, etc.
Levels keys can be: beginner, intermediate, expert, etc.

Each user can play of 1 or more instrument. For each played instrument (relationship), we attribute a level with level_id.

I am trying to get a list of instruments for a given user, with the corresponding level. Here is the JSON returned for a user with 1 instrument:

"instruments":[
    {
        "id":1,
        "key":"guitar",
        "pivot":{
            "user_id":1,
            "instrument_id":1,
            "level_id":1
        },
        "level":[
            {
                "id":1,
                "key":"beginner",
                "pivot":{
                    "instrument_id":1,
                    "level_id":1
                }
            }
        ]
    }
]

My issue here is that level is an array, but I want only one level attributed to each played instrument. I believe this is because I used a belongsToMany relationship, but that was the only way I found to be able to pass through the intermediate table (instruments_users).

In Laravel I configured my Eloquent Models as follow:

User.php

public function instruments() {
    return $this->belongsToMany(
        Instrument::class,
        'instruments_users'
    )->withPivot('level_id');
}

Instrument.php

protected $with = ['level'];

public function users() {
    return $this->belongsToMany(
        User::class,
        'instruments_users'
    )->withPivot('level_id');
}

public function level() {
    return $this->belongsToMany(
        Level::class,
        'instruments_users'
    );
}

Level.php

public function instruments() {
    return $this->belongsToMany(
        Instrument::class,
        'instruments_users'
    );
}

Laravel doesn't currently support ternary relationships. There's a workaround here. Here you can also simplify to user - level and have each level be instrument specific and then each user will be associated to a level and to an instrument via that level — apokryfos
Why not drop the levels table entirely and add the level to the instruments_users pivot table? — DigitalDrifter
@DigitalDrifter I thought about that but I want to be able to edit/display that list easily. Plus, the keys are going to be used in translation files and the DB table will be a good reference point. — Arnaud B
@apokryfos the workaround doesn't seem to fix my issue, which is that I get level as an array with one element (because there can be only one element) instead of a single element. As for having instrument specific levels, that defeats the modularity of levels in my case. — Arnaud B

Rob Biermann Rob Biermann · Accepted Answer · 2020-04-09T17:12:27

I think you are better off defining different kind of relationships:

Tables and models:

users >> Model User
    - id
    - name

instruments >> Model Instrument
    - id
    - key

instruments_users >> Model InstrumentUser
    - id
    - user_id
    - instrument_id
    - level_id

levels >> Model Level
    - id
    - key

Model User:

function userInstruments()
{
    //One user can have many user_instruments
    return $this->hasMany(App\InstrumentUser::class);
}

Model UserInstrument:

function instrument()
{
    //One user_instrument belongs to one instrument 
    return $this->belongsTo(App\Instrument::class);
}

function level()
{
    //One user_instrument belongs to one level
    return $this->belongsTo(App\Level::class);
}

With this setup, you can get all the data required with a query like this:

$user = User::with(['userInstruments.instrument', 'userInstruments.level'])->find($id);

$users = User::with(['userInstruments.instrument', 'userInstruments.level'])->get();

The output should look something like this:

//User
{
    "id": 1,
    "name": "Darth",
    "user_instruments": [
        {
            "id": 1,
            "instrument_id": 1,
            "instrument": {
                "id": 1,
                "name": "clarinet",
            },
            "level_id": 1,
            "level": {
                "id": "1",
                "key": "Noob"
            },
            "user_id": 1
        }
    ]
}

I hope this helps.

Laravel 3 way intermediate pivot table

1 Answers