mikecou
6 months ago

Retrieve Eloquent Relationships from a Pivot table while doing a belongsToMany

Posted 6 months ago by mikecou

I have two tables, Car and Driver which are joined through a belongs to many relationship in eloquent. This works just dandy for me.

I want to also create a model for the pivot table in the middle and then have that do a lookup on another table that provides more information.

The Car would be a Honda CRV and the Driver would be Bob, but the pivot table contains an ID that links up with another table to define the relationship like if they are the driver, passenger, or riding on hood.

I have tried the ->using(MODEL) and that didn't seem to work, also tried the withPivot(COLUMNS) and that would return values that were directly on the pivot table, but not allow me to then look up another value.

The relationships work just fine except for allowing me to pull this extra bit of information from the table.

In essence I am trying to do this:

select 
car_brand, 
driver_name, 
type_of_rider 
from driver
inner join pivot_table 
    on driver_id = pivot_driver_id
inner join car 
    on pivot_car_id = car_id
inner join rider_type 
    on pivot_type_of_rider_id = type_of_rider_id

Is there a way to accomplish this?

class Car extends Model
{
    public function car_drivers()
    {
        return $this->belongsToMany('App\Driver', 'pivot_car_driver', 'car_id', 'driver_id')->using('App\Models\PivotCarDriver')->withPivot('pivot_column1', 'pivot_column2');
    }
}
class Driver extends Model
{
    public function driver_cars()
    {
        return $this->belongsToMany('App\Car', 'pivot_car_driver', 'driver_id', 'car_id')->using('App\Models\PivotCarDriver')->withPivot('pivot_column1', 'pivot_column2');
    }

}
Car Table:
car_id
car_brand

Driver Table:
driver_id
driver_name

Pivot Table
pivot_car_id
pivot_driver_id
pivot_type_of_rider_id

Rider Type Table:
type_of_rider ID
type_of_rider

Please sign in or create an account to participate in this conversation.