Laravel Has Many Relationship with a OR condition-CodePudding

I need an help understanding Eloquent has many relation in this particular scenario.

This is the scenario:

Airport Table

| id  | code | name |
---------------------
|  1  |  XXX | XXX  |
---------------------

Route Table

| id  | from | to |
-------------------
|  1  |  1   |  2 |
--------------------

As above, ONE airport table 'id' can be a foreign key in 'from' OR 'to' Route Table column.

What I'd like to do is to retrieve all records from Airports table with its routes (Where the airport can appear as a departure or as a destination).

For example:

[
  {
     'id': 1,
     'code': 'JFK',
     'name': 'John F. Kennedy International Airport'
     'routes' : [
         {
            'id': 1,
            'from': 1 //JFK airports table id
            'to': 22 //somewhere
         },
         {
            'id': 1,
            'from': 334 //somewhere
            'to': 1 //JFK airports table id
         }
     ]
  }
]

Obviously I know how to do it with a SQL query or using Eloquen but what I want to understand is if it can be done natively in the Model by expressing a relationship.

CodePudding user response：

Maybe adding to the relationship

->orWhereColumn('Route.to','AirportTable.id')

CodePudding user response：

The "Eloquent has many relation" you are imagining does not exist, and also does not make sense as a relation. Pivot table columns have semantic meaning, just like in your case of from and to.

Firstly, this is not a HasMany, it is a BelongsToMany (Many to Many) in Eloquent speak. If you want to do this purely with relations, then you will need to define both of them. Airports are destinations when they are in the to column, and Airports are origins when they are in the from column.

To get all routes an Airport is a part of, we can simply merge the results of the two relations.

The $appends attribute is used to automatically append the data when serializing.

The $with attribute is used to always eagerload these relations when retrieving Airports from the database.

Airport.php

protected $appends = ['routes']; 

protected $with = ['destinations', 'origins'];

public function destinations()
{
    return $this->belongsToMany(Airport::class, 'routes', 'from', 'to')
        ->withPivot(['id', 'from', 'to']);;
}

public function origins()
{
    return $this->belongsToMany(Airport::class, 'routes', 'to', 'from')
        ->withPivot(['id', 'from', 'to']);
}


public function getRoutesAttribute()
{
    return $this->destinations->pluck('pivot')
                ->merge($this->origins->pluck('pivot'));
}

Now if you were to do:

Airport::query()->take(5)->toArray();

You would see all of the routes associated on each of the Airport results.