Laravel – Eloquent relation whereHas one or more other relations

Solution:

There is method to retrieve a “distant relationship with an intermediary” and it is called Has Many Through.

There is also a concrete example on how to use it which includes Post, Country and User, but I think it will be sufficient to give you a hint on how to create games relationship inside of a Club model. Here is a link, but when you open it, search for hasManyThrough keyword and you will see an example.

P.S: With right keys naming you could achieve it with:

public function games()
{
    return $this->hasManyThrough('App\Models\Games', 'App\Models\Teams');
}

EDIT #01

Since you have 2 types of teams, you can create 2 different relationships where each relationship will get you one of the type you need. Like this:

public function gamesAsHome()
{
    return $this
        ->hasManyThrough('App\Models\Games', 'App\Models\Teams', 'club_id', 'home_id');
}

public function gamesAsGuests()
{
    return $this
        ->hasManyThrough('App\Models\Games', 'App\Models\Teams', 'club_id', 'guest_id');
}

EDIT #02

Merging Relationships: To merge these 2 relationships, you can use merge() method on the Collection instance, what it will do is, it will append all the records from second collection into the first one.

$gamesHome = $model->gamesAsHome;
$gamesGuests = $model->gamesAsGuests;
$games = $gamesHome->merge($gamesGuests);

return $games->unique()->all();

Thanks to @HCK for pointing out that you might have duplicates after the merge and that unique() is required to get the unique games after the merge.

EDIT #03

sortBy also offers a callable instead of a attribute name in cases where Collection contains numerical indexing. You can sort your Collection like this:

$merged->sortBy(function($game, $key) {
    return $game->created_at;
});