How to get further detail of table using with keywork in laravel?

Question

I have a one to many relation based on two tables users and games.There is also a pivot table users_games (linking user_id to games).

I am trying to fetch a single record based on game_id and user_id using the script below

$GameInfo = User::with(['games' => function ($query) use($request)
        {
            $query->where('game_id', $request->game_id);
        }])->whereHas('games', function ($query) use($request)
        {
            $query->where('game_id', '=', $request->game_id);
        })->find(request()->user()->id);

Following is the response of the script above (I am using the API Resource)

{
    "data": [
        {
            "id": 4,
            "name": "eum",
            "type_id": 2,
            "created_at": "2018-10-30 11:23:28",
            "updated_at": "2018-10-30 11:23:28",
            "pivot": {
                "user_id": 2,
                "game_id": 4
            }
        }
    ]
}

Actully now I want to fetch highscore of that game also which is stored inside the user_games table. I tried a lot but still fail to get the desired result.

I would like to request you to kindly guide me how I can do that. I would appreciate it.

Answer 1

In your eloquent relationship, you can use the withPivot() function :

  return $this->belongsToMany('App\Role')->withPivot('column1', 'column2');

but it will always return the pivot column, if you want to do it only in your current Query, you might do this :

  $GameInfo = User::with(['games' => function ($query) use($request)
    {
        $query->withPivot('highscore')->where('game_id', $request->game_id);
    }])->whereHas('games', function ($query) use($request)
    {
        $query->where('game_id', '=', $request->game_id);
    })->find(request()->user()->id);