Database One-to-Many with two foreign key fields in Laravel

After some more digging into the source code I found there is a way to actually keep my database schema as it is and achieve what I want (at least in Laravel 4). I posted my problem in github and Taylor Otwell (creator of the framework) gave me the correct answer: https://github.com/laravel/framework/issues/1272

Quoting him, it should be as easy as this:

class Team extends Eloquent  {    public function allMatches()    {        return $this->hasMany('Match', 'visitant_id')->orWhere('local_id', $this->id);    }}

And then...

$team = Team::find(2);$matches = $team->allMatches;

This is one of those famous database design problems. Friendship relationships, for instance, suffer from that same difficulty. Since you are using Eloquent, I would suggest you to stick with many to many approach and have an extra boolean column local on your intermediate table

class Match extends Eloquent {    public $includes = array('team'); // Always eager load teams    public function teams() {        return $this->has_many_and_belongs_to('team')->with('local');    }    public function get_local() {        foreach ($this->teams as $team) {            if ($team->pivot->local) return $team;        }    }    public function get_visitant()   {        foreach ($this->teams as $team) {            if (!$team->pivot->local) return $team;        }    }}class Team extends Eloquent  {    public function matches() {        return $this->has_many_and_belongs_to('match')->with('local');    }    // I'm doing separate queries here because a team may have    // hundreds of matches and it's not worth looping through    // all of them to retrieve the local ones    public function matches_as_local()  {        return $this->has_many_and_belongs_to('match')->with('local')            ->where('pivot_local', '=', 1);    }    public function matches_as_visitant()  {        return $this->has_many_and_belongs_to('match')->with('local')            ->where('pivot_local', '=', 0);    }}

Obs:

The method has_many_and_belongs_to(...)->with('field') has nothing to do with eager loading. It tells Eloquent to load the intermediate table column field and put that in the pivot.

Usage:

$match = Match::find(1);$match->local; // returns local team$match->visitant; // returns visitant team$team = Team::find(1);$team->matches; // returns all matches$team->matches_as_local; // ...$team->matches_as_visitant; // ...foreach ($team->matches as $match) {    if ($match->pivot->local) {        // put nice local icon here    } else {        // put nice visitant icon here    }}