Refactor Laravel Query

Question

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

I don't feel like there'd be a simple way of achieving this with a simple query, but I am also curious to know :) — Mozammil
– Mozammil, Commented Jan 22, 2019 at 6:57
each table is interconnected with unique data elements if thats what youre referring too. @Mozammil — LeviZoesch
– LeviZoesch, Commented Jan 22, 2019 at 6:59

Styx · Accepted Answer · 2019-01-22 07:38:53Z

1

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();

answered Jan 22, 2019 at 7:38

Styx

10.1k8 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

LeviZoesch Over a year ago

Wonderful. Only problem ive noticed is it doesnt pull all players that have played in the past 30 days. I've verified a few users have played, yet doesnt return as such. Ideas? @Styx

Styx Over a year ago

@Kray Told you your dates comparison seems weird :) Could you show a couple of rows from your DB with dates? Better yet — start a new question about that.

LeviZoesch Over a year ago

I added the tables in OP.

Styx Over a year ago

@Kray Better start new question about comparison of dates with non-standard format, I'd be glad to help.

LeviZoesch Over a year ago

im not sure what to ask, and not sure it warrants a new question?

|

Oluwafemi Sule · Accepted Answer · 2019-01-22 07:38:36Z

Basically, I would reduce the query to its SQL variant to get directly at its core. The essence of the query is

select `x` FROM foo WHERE id IN (
  select distinct bar.id from bar join baz on bar.id = baz.id);

This can be interpreted in Eloquent as:

$thirtyDaysAgo = Carbon::now()->subDays(30)->format('m/d/Y');

$playerIds = DB::table('pickup_games')
  ->select('pickup_games.player_id')
  ->join(
      'pickup_results',
      'pickup_results.pickupRecordLocatorID',
      'pickup_games.recordLocatorID')
  ->where('pickupDate', '>=', $thirtyDaysAgo)
  ->orderBy('pickupDate', 'ASC')
  ->distinct('pickup_games.player_id');


$activePlayers = DB::table('players')
      ->select('player_name')
      ->whereIn('player_id', $playerIds);

//>>>$activePlayers->toSql();
//select "player_name" from "players" where "player_id" in (
//  select distinct * from "pickup_games" 
//  inner join "pickup_results" 
//    on "pickup_results"."pickupRecordLocatorID" = "pickup_games"."recordLocatorID" 
//  where "pickupDate" >= ? order by "pickupDate" asc
//)

From the resulting query, it may be better to refactor the join as relationship between the Eloquent model for pickup_games and pickup_results. This will help to further simplify $playerIds.

Collectives™ on Stack Overflow

Refactor Laravel Query

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related