Im trying to get top five players and I use join to get each players information base on personId, but my query returns duplicate data. I cant figure out why it duplicating the record, any help would be much appriciated. Thanks
DB::table('person_competition_statistics as pcs')
->where('pcs.competitionId', $competitionId)
->where('pcs.teamId', $teamId)
->orderBy('pcs.sStealsAverage', 'desc')
->rightJoin('players as player', 'pcs.personId', '=', 'player.personId')
->select(['pcs.personId', 'pcs.sStealsAverage', 'player.firstName', 'player.familyName', 'player.playingPosition', 'player.image_thumb'])
->take(5)
->get();
Here is the result
#items: array:5 [▼
0 => {#444 ▼
+"personId": 29872
+"sStealsAverage": 1.24
+"firstName": "Rhys"
+"familyName": ""
+"playingPosition": ""
+"image_thumb": ""
}
1 => {#438 ▼
+"personId": 29872
+"sStealsAverage": 1.24
+"firstName": "Rhys"
+"familyName": ""
+"playingPosition": ""
+"image_thumb": ""
}
2 => {#439 ▼
+"personId": 29872
+"sStealsAverage": 1.24
+"firstName": "Rhys"
+"familyName": ""
+"playingPosition": "GRD"
+"image_thumb": ""
}
3 => {#441 ▼
+"personId": 29872
+"sStealsAverage": 1.24
+"firstName": "Rhys"
+"familyName": ""
+"playingPosition": "GRD"
+"image_thumb": ""
}
4 => {#435 ▼
+"personId": 29872
+"sStealsAverage": 1.24
+"firstName": "Rhys"
+"familyName": ""
+"playingPosition": "GRD"
+"image_thumb": ""
}
I've used unique to return unique personId and array_slice to get the top 5, below is my code.
$spg = DB::table('person_competition_statistics as pcs')
->where('pcs.competitionId', $competitionId)
->where('pcs.teamId', $teamId)
->orderBy('pcs.sStealsAverage', 'desc')
->join('players as player', 'pcs.personId', '=', 'player.personId')
->select(['pcs.personId', 'pcs.sStealsAverage', 'player.firstName', 'player.familyName', 'player.playingPosition', 'player.image_thumb'])
->get();
$spg = $spg->unique('personId');
$spg = array_slice($spg->values()->all(), 0, 5, true);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With