CODEWITHSUNDEEP
phpmysqlsqllaravellaravel-5
PenAndPapers
PenAndPapers

Reputation: 2108

Laravel sql take top records and get it's field values average

how can I be able to get top records e.g top 10 top 5 top 3, I've tried to use AVG on my query but it returns the whole average of a persons record.

SQL

if( !$field ) {
    $select = [
        DB::raw('AVG(sFieldGoalsPercentage) AS sFieldGoalsPercentage'), 
        DB::raw('AVG(sThreePointersPercentage) AS sThreePointersPercentage'), 
        DB::raw('AVG(sTwoPointersPercentage) AS sTwoPointersPercentage')
    ];
}

$player1 = DB::table('person_match_statistics')
    ->where('competitionId', $competitionId)
    ->where('personId', $player1Id)
    ->where('periodNumber', 0)
    ->orderBy('matchId', 'DESC')
    ->select($select)
    ->take($lastGame)
    ->get();

Array

0 => {#543 ▼
  +"sFieldGoalsPercentage": 0.56
  +"sThreePointersPercentage": 0.5
  +"sTwoPointersPercentage": 0.63
}
1 => {#536 ▼
  +"sFieldGoalsPercentage": 0.4
  +"sThreePointersPercentage": 0.4
  +"sTwoPointersPercentage": 0.4
}
2 => {#539 ▼
  +"sFieldGoalsPercentage": 0.38
  +"sThreePointersPercentage": 0.2
  +"sTwoPointersPercentage": 0.67
}

Upvotes: 2

Views: 566

Answers (1)

Alexey Mezenin
Alexey Mezenin

Reputation: 163898

You could use avg() collection method. Get top ten records:

$player1 = DB::table('person_match_statistics')
    ->where('competitionId', $competitionId)
    ->where('personId', $player1Id)
    ->where('periodNumber', 0)
    ->latest('matchId')
    ->select($select)
    ->take(10)
    ->get();

Then count an average without executing any new queries using take() and avg() collection methods. For example:

$top10 = $player1->avg('column');
$top5 = $player1->take(5)->avg('column');
$top3 = $player1->take(3)->avg('column');

Upvotes: 2

Related Questions