Many to many relationship with multiple ids

Question

I have a Route and Terminus model. A route can only have two Terminus but the Terminus has many routes. The fields are as below:

termini
-id
-lat_long

routes
-id
-main_terminus_id
-auxiliary_terminus_id

I am using query builder to get the information I need but I am missing out on the 'auxiliary' terminus details. My query is as follows:

$routes = DB::table('organization_route')
        ->join('routes','organization_route.route_id','=','routes.id')
        ->join('terminals', function ($join){
            $join->on(function($query){
                $query->on('terminals.id', '=', 'routes.mainTerminalId')
                    ->orOn('terminals.id', '=', 'routes.auxiliaryTerminalId');
            });
        })->get(['routes.id','routes.mainTerminalId','routes.auxiliaryTerminalId','routes.distance','terminals.*']);

What relationship best describes this? How can I retrieve routes with all terminus information-both main and auxiliary?

Answer 1

Terminus Model

public function mainTerminusRoutes(){
    return $this->hasMany(App\Route::class, 'main_terminus_id', 
       'id');
}

public function auxiliaryTerminusRoutes(){
    return $this->hasMany(App\Route::class, 
       'auxiliary_terminus_id', 'id');
}

Route Model

public function mainTerminus(){
    return $this->belongsTo(App\Terminus::class, 
         'main_terminus_id', 'id');
}

public function auxiliaryTerminus(){
    return $this->belongsTo(App\Terminus::class, 
         'auxiliary_terminus_id', 'id');
}

Controller: get all the routes(and eager load the relations)

$routes = Route::with(['mainTerminus', 'auxiliaryTerminus'])->get();

You may access the relations as follows

foreach($routes as $route){
    $m_id = $route->mainTerminus->id;
    $a_id = $route->auxiliaryTerminus->id;
}