Laravel Eloquent - 使用关系将查询与单一查询相结合

Question

I'm working on a project where I'm using Laravel Eloquent to fetch all my data from my database.

I'm storing basketball clubs, teams and players in different tables. Now I want to select all basketball players that belong to a team that belongs to a club.

I created a relation in the Club model:

public function basketballPlayers()
    {
        return $this->hasManyThrough('App\Models\Basketball\BasketballPlayer','App\Models\Basketball\BasketballTeam','id','team_id');
    }

And a user relation in the BasketballPlayer model:

public function user()
    {
        return $this->hasOne('App\User','id','user_id');
    }

Now when I execute the following command, DB::getQueryLog() returns 3 Queries.

Club::findOrFail($club_id)->basketballPlayers()->with('user')->get();

But when I execute the following command without using relations, DB::getQueryLog() returns only 1 query.

$players = BasketballPlayer::Join('basketball_teams','basketball_teams.id','=','basketball_players.team_id')
            ->join('users','users.id','=','basketball_players.user_id')
            ->where('basketball_teams.club_id','=',$authUser->club_id)
            ->get(['basketball_players.*','users.firstname','users.lastname','basketball_teams.name']);

I don't need all the data from the club and the user (see ->get(...) in the Join query).

Is it possible to achieve the same result like in the "long" manual Join-query using the much cleaner and simpler Eloquent relation approach?

Thanks for your help!

Answer 1

You can read less data using with (not all user columns as example) by this

Club::findOrFail($club_id)
    ->basketballPlayers()
    ->with('user:id,firstname,lastname')   // <- here is change
    ->get();

The id (and foreign keys) column is important. You should be also able to make nested with in following way (I write code from head - so please test it):

Club::findOrFail($club_id)
    ->with('BasketballPlayer:id,user_id')
    ->with('BasketballPlayer.user:id,firstname,lastname')   
    ->get();