Refactor Laravel Query

I have a query that I have built, and I am trying to understand how I can achieve the same thing but in one single query. I am fairly new to Laravel and learning. Anyway someone could help me understand how I can achieve what I am after?

$activePlayerRoster = array();

$pickupGames = DB::table('pickup_games')
  ->where('pickupDate', '>=', Carbon::now()->subDays(30)->format('m/d/Y'))
  ->orderBy('pickupDate', 'ASC')
  ->get();

foreach ($pickupGames as $games) {

  foreach(DB::table('pickup_results')
            ->where('pickupRecordLocatorID', $games->recordLocatorID)
            ->get() as $activePlayers) {

    $activePlayerRoster[] = $activePlayers->playerID;
    $unique = array_unique($activePlayerRoster);

  }

}

$activePlayerList = array();

foreach($unique as $playerID) {

  $playerinfo = DB::table('players')
                  ->select('player_name')
                  ->where('player_id', $playerID)
                  ->first();
  $activePlayerList[] = $playerinfo;

}

return $activePlayerList;

pickup_games checkSumID pickupDate startTime endTime gameDuration winningTeam recordLocatorID pickupID

1546329808471 01/01/2019 08:03 am 08:53 am 50 Minute 2 f47ac0fc775cb5793-0a8a0-ad4789d4 216

pickup_results

id checkSumID playerID team gameResult pickOrder pickupRecordLocatorID

1 1535074728532 425336395712954388 1 Loss 0 be3532dbb7fee8bde-2213c-5c5ce710

Solution

First, you should try to write SQL query, and then convert it to Laravel's database code.

If performance is not critical for you, then it could be done in one query like this:

SELECT DISTINCT players.player_name FROM pickup_results
LEFT JOIN players ON players.player_id = pickup_results.playerID
WHERE EXISTS (
  SELECT 1 FROM pickup_games
  WHERE pickupDate >= DATE_FORMAT(SUBDATE(NOW(), INTERVAL 30 DAY), '%m/%d/%Y')
    AND pickup_results.pickupRecordLocatorID = recordLocatorID
)

Here I'm assuming you know what you're doing with this dates comparison, because it looks weird to me.

Now, let's convert it to Laravel's code:

DB::table('pickup_results')
  ->select('players.player_name')->distinct()
  ->leftJoin('players', 'players.player_id', '=', 'pickup_results.playerID')
  ->whereExists(function ($query) {
    $query->select(DB::raw(1))
          ->from('pickup_games')
          ->where('pickupDate', '>=', Carbon::now()->subDays(30)->format('m/d/Y'))
          ->whereRaw('pickup_results.pickupRecordLocatorID = recordLocatorID'); 
  })
  ->get();