Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel Join returns duplicates data

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": ""
      }
like image 649
PenAndPapers Avatar asked Jan 04 '23 20:01

PenAndPapers


1 Answers

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);
like image 87
PenAndPapers Avatar answered Jan 08 '23 07:01

PenAndPapers