Relation between two models with intermediate table

Question

have spent hours on trying and failing, reading the documentation and trying to translate the documentation to a challenge I am facing in Laravel.

The worst part is, I don't think the issue is that hard to solve, but somehow my head won't work properly.

The Laravel app I am working is a Tour de France Fantasy Manager. I have Rider and Stage models, and I have an intermediate RiderStageStatus model where I will store information about whether a rider is joining the stage. I want to implement stage results as well, so I guess the issue will turn up again for that later on.

However, my database looks like this:

riders
- id
- name
- ...

stages
- id
- departure
- arrival
- ...

statuses
- id
- name
- ...

rider_stage_statuses
- id
- rider_id
- stage_id
- status_id

So I should be able to get riders and their status for a certain stage Stage::find(1)->riders and also get statuses for each stage of a rider Rider::find(1)->stages.

But I cannot make the right relations in the models. What am I doing wrong?

`rider_stage_statuses` looks like the pivot table of a ternary relationship. Laravel does not support those so having `RiderStageStatus` is probably inevidable. I'd argue that in your particular case a ternary is not strictly necessary but if you still want to go down that path prepare for additional challanges — apokryfos, Jul 02 '19 at 13:01
@apokryfos Interesting, I would preferably to the smartest thing in the long run. How should I get started if I should avoid a ternary relationship? — rebellion, Jul 02 '19 at 15:29
Well personally I'm looking at the `status` table suspiciously. It really depends on what your data model is but I would expect the status to be directly related to a rider rather than a rider/stage assuming I have understood what the status is correctly. — apokryfos, Jul 02 '19 at 16:05
@apokryfos I understand. The issue is that a rider might drop out of one stage because of injury. But he will be attending the next stage. Thus I need to add a status per stage per rider. — rebellion, Jul 02 '19 at 18:14

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

Solution 1

The documentation shows you can add an extra argument to tell Laravel what table to use as pivot table.

Build your classes as follows:

Class rider

class Rider {

    public function stages() {
        return $this->belongsToMany(Stage::class, 'rider_stage_statuses');
    }
}

Class stage

class Stage {
    
    public function riders() {
        return $this->belongsToMany(Rider::class, 'rider_stage_statuses');
    }
}

Solution 2

We could also fix your code by fixing the intermediate table.

Whe should structure your code like this:

Class rider

class Rider {

    public function stages() {
        return $this->belongsToMany(Stage::class)->using(RiderStageStatus::class);
    }
}

Class stage

class Stage {
    
    public function riders() {
        return $this->belongsToMany(Rider::class)->using(RiderStageStatus::class);
    }
}

Relation between two models with intermediate table

1 Answers1

Solution 1

Class rider

Class stage

Solution 2

Class rider

Class stage