Reputation: 2108
So I have a query that return all the stats of a player from their last season and current season. What I want to do is get the average of a player from last season and current season and make in to a one object only. Below is my query and sample result.
Query
public static function getAllTimeLeaders($request) {
$competitionIds = self::getLasTwoSeason();
$data = DB::table('leader_board_stats as lbs')
->select([
'sGamesStarted',
'sMinutesAverage',
'sPointsAverage',
'sFieldGoalsMadeAverage',
'sFieldGoalsAttemptedAverage',
'sFieldGoalsPercentage',
'sTwoPointersMadeAverage',
'sTwoPointersAttemptedAverage',
'sThreePointersMadeAverage',
'sThreePointersAttemptedAverage',
'sThreePointersPercentage',
'sFreeThrowsMadeAverage',
'sFreeThrowsAttemptedAverage',
'sReboundsTotalAverage',
'sAssistsAverage',
'sStealsAverage',
'sBlocksAverage',
'sTurnoversAverage',
'sFoulsPersonalAverage',
'firstName',
'familyName',
'personId',
'teamId',
'competitionId'
])
->where('lbs.periodNumber', '0')
->whereIn('lbs.competitionId', [$competitionIds[0]->competitionId, $competitionIds[1]->competitionId])
->get();
echo '<pre>';
var_dump($data);
}
Sample of returned value
[
[0]=> object(stdClass)#262 (24) {
["sGamesStarted"]=>
int(6)
["sMinutesAverage"]=>
float(17.66)
["sPointsAverage"]=>
float(7.71)
["sFieldGoalsMadeAverage"]=>
float(2.59)
["sFieldGoalsAttemptedAverage"]=>
float(5.62)
["sFieldGoalsPercentage"]=>
float(0.46)
["sTwoPointersMadeAverage"]=>
float(1.47)
["sTwoPointersAttemptedAverage"]=>
float(2.53)
["sThreePointersMadeAverage"]=>
float(1.12)
["sThreePointersAttemptedAverage"]=>
float(3.09)
["sThreePointersPercentage"]=>
float(0.36)
["sFreeThrowsMadeAverage"]=>
float(1.41)
["sFreeThrowsAttemptedAverage"]=>
float(1.76)
["sReboundsTotalAverage"]=>
float(2.85)
["sAssistsAverage"]=>
float(1.71)
["sStealsAverage"]=>
float(0.68)
["sBlocksAverage"]=>
float(0.21)
["sTurnoversAverage"]=>
float(0.91)
["sFoulsPersonalAverage"]=>
float(2.35)
["firstName"]=>
string(3) "Person1"
["familyName"]=>
string(8) "Familyname"
["personId"]=>
int(28831)
["teamId"]=>
int(3749)
["competitionId"]=>
int(9224)
},
[88]=> object(stdClass)#527 (24) {
["sGamesStarted"]=>
int(11)
["sMinutesAverage"]=>
float(23.28)
["sPointsAverage"]=>
float(9.5)
["sFieldGoalsMadeAverage"]=>
float(3.07)
["sFieldGoalsAttemptedAverage"]=>
float(7.97)
["sFieldGoalsPercentage"]=>
float(0.38)
["sTwoPointersMadeAverage"]=>
float(1.4)
["sTwoPointersAttemptedAverage"]=>
float(3.47)
["sThreePointersMadeAverage"]=>
float(1.67)
["sThreePointersAttemptedAverage"]=>
float(4.5)
["sThreePointersPercentage"]=>
float(0.37)
["sFreeThrowsMadeAverage"]=>
float(1.7)
["sFreeThrowsAttemptedAverage"]=>
float(2.2)
["sReboundsTotalAverage"]=>
float(3.57)
["sAssistsAverage"]=>
float(1.43)
["sStealsAverage"]=>
float(0.93)
["sBlocksAverage"]=>
float(0.23)
["sTurnoversAverage"]=>
float(0.97)
["sFoulsPersonalAverage"]=>
float(2.77)
["firstName"]=>
string(3) "Person1"
["familyName"]=>
string(8) "Familyname"
["personId"]=>
int(28831)
["teamId"]=>
int(3749)
["competitionId"]=>
int(2254)
}
]
Upvotes: 0
Views: 814
Reputation: 1277
Try: avg() is easier and cleaner.
Docs: https://laravel.com/docs/5.4/collections#method-avg
Upvotes: 1
Reputation: 2108
Finally I figured it out I just need to use DB::raw and AVG of each stats fields then group it by personId.
public static function getAllTimeLeaders($request) {
$competitionIds = self::getLasTwoSeason();
$data = DB::table('leader_board_stats as lbs')
->select([
DB::raw('AVG(sGamesStarted)'),
DB::raw('AVG(sMinutesAverage)'),
DB::raw('AVG(sPointsAverage)'),
DB::raw('AVG(sFieldGoalsMadeAverage)'),
DB::raw('AVG(sFieldGoalsAttemptedAverage)'),
DB::raw('AVG(sFieldGoalsPercentage)'),
DB::raw('AVG(sTwoPointersMadeAverage)'),
DB::raw('AVG(sTwoPointersAttemptedAverage)'),
DB::raw('AVG(sThreePointersMadeAverage)'),
DB::raw('AVG(sThreePointersAttemptedAverage)'),
DB::raw('AVG(sThreePointersPercentage)'),
DB::raw('AVG(sFreeThrowsMadeAverage)'),
DB::raw('AVG(sFreeThrowsAttemptedAverage)'),
DB::raw('AVG(sReboundsTotalAverage)'),
DB::raw('AVG(sAssistsAverage)'),
DB::raw('AVG(sStealsAverage)'),
DB::raw('AVG(sBlocksAverage)'),
DB::raw('AVG(sTurnoversAverage)'),
DB::raw('AVG(sFoulsPersonalAverage)'),
'firstName',
'familyName',
'personId',
'teamId',
'competitionId'
])
->where('lbs.periodNumber', '0')
->whereIn('lbs.competitionId', [$competitionIds[0]->competitionId, $competitionIds[1]->competitionId])
->groupBy('lbs.personId')
->get();
dd($data);
}
Upvotes: 0