Eloquent Multiple Foreign Keys from same table in two columns

Question

I am having issues calling a departure ICAO and arrival ICAO from my schedules table. Laravel keeps giving me errors that my relationships are screwed up. Here is the code.

Schema::create('airports', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('city');
        $table->string('country');
        $table->string('iata');
        $table->string('icao');
        $table->double('lat');
        $table->double('lon');
        $table->longText('data')->nullable(); //JSON Data for All gate information for the system.
        $table->softDeletes();
    });
Schema::create('schedule_templates', function (Blueprint $table) {
        $table->increments('id');
        $table->string('code');
        $table->string('flightnum');
        $table->integer('depicao')->unsigned();
        $table->foreign('depicao')->references('id')->on('airports')->onDelete('cascade');
        $table->integer('arricao')->unsigned();
        $table->foreign('arricao')->references('id')->on('airports')->onDelete('cascade');
        $table->string('aircraft')->nullable();
        $table->boolean('seasonal');
        $table->date('startdate');
        $table->date('enddate');
        $table->time('deptime');
        $table->time('arrtime');
        $table->integer('type');
        $table->boolean('enabled');
        $table->text('defaults');
        $table->timestamps();
        $table->softDeletes();
    });

Here are the Models

class ScheduleTemplate extends Model
{
    public $table = "schedule_templates";

    public function depicao()
    {
        return $this->hasOne('App\Models\Airport', 'depicao');
    }
    public function arricao()
    {
        return $this->hasOne('App\Models\Airport', 'arricao');
    }
}

class Airport extends Model
{
    //
    public $timestamps = false;

    public function schedules()
    {
        return $this->belongsToMany('App\ScheduleTemplate');
    }
}

When I attempt to query using the following code, I get the error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'vaos_airports.depicao' in 'where clause' (SQL: select * from vaos_airports where vaos_airports.depicao in (1))

$schedules = ScheduleTemplate::with('depicao')->with('arricao')->get();

The end goal is to pull the results into a table. Here is that code if interested.

@foreach($schedules as $s)
     <tr>
          <td>{{$s->code}}</td>
          <td>{{$s->flightnum}}</td>
          <td>{{$s->depicao()->name}}</td>
          <td>{{$s->arricao()->name}}</td>
          <td>{{$s->aircraft}}</td>
          <td>{{$s->seasonal}}</td>
          <td>{{$s->type}}</td>
     </tr>
@endforeach

EDIT:

I fixed the relationship problem Apparently I had them swapped. Here are the updated Model classes

class ScheduleTemplate extends Model
{
    public $table = "schedule_templates";

    public function depicao()
    {
        return $this->belongsTo('App\Models\Airport', 'depicao');
    }
    public function arricao()
    {
        return $this->belongsTo('App\Models\Airport', 'arricao');
    }
}

class Airport extends Model
{
    //
    public $timestamps = false;

    public function schedules()
    {
        return $this->hasMany('App\ScheduleTemplate');
    }
}

The error now lies in the view file. I will either get a BelongsTo error:

Undefined property: Illuminate\Database\Eloquent\Relations\BelongsTo::$name

or this if I have arricao or depicao without "()"

Trying to get property of non-object

Answer 1

The point is, that the second argument of a relationship should be the foreign key, and the second the local key.

From the docs:

return $this->hasOne('App\Phone', 'foreign_key', 'local_key');

So in your case, try this:

public function depicao()
{
    return $this->hasOne('App\Models\Airport', 'id', 'depicao');
}
public function arricao()
{
    return $this->hasOne('App\Models\Airport', 'id', 'arricao');
}

Update The errors are thrown because you have the same column name for this relationship. In my opinion, two solutions:

1. Try to get the first object out of the relationship, like this. But note here: eager loading will not work!

   <td>{{$s->depicao()->first()->name}}</td> <td>{{$s->arricao()->first()->name}}</td>

2. Rename your relationships or the columns, so they don't overlap current column names. This is by far the best option.

For example, you could change the columns to depeciao_id and arricao_id, this also indicates that the columns are referenced to another table with corresponding ID, which is more descriptive.