using Active record with SUM() AND CASE

i am converting the following query using yii2 active record


select T.name,(SELECT 

    (SUM(CASE WHEN BMS.winner_id=T.id THEN BMS.points_won ELSE BMS.points_lost END)-

    SUM(CASE WHEN BMS.winner_id=T.id THEN BMS.points_lost ELSE BMS.points_won END))

    /(COUNT(BMS.id)) FROM

     badminton_match_score BMS 

     JOIN badminton_matches M ON (M.id=BMS.match_id) where M.team_one=T.id OR M.team_two=T.id and M.winner_id is not null) as AVG_SCORE 

     from badminton_teams T order by AVG_SCORE DESC

and this is what i ended up with, creating a function getAvgScore() inside the Teams model and calling it inde the loop for each team, which is not exactly what i wanted although it gives correct results, but i wanted the calculations to be done via query using activerecord, can any one suggest a better way to implement this query so that i do not have to use php to calculate the average score.




 public function getAvgScore(){


$sum_query  =   MatchScore::find()

                ->select([new \yii\db\Expression('SUM(CASE WHEN [[badminton_match_score.winner_id]]='.$this->id.' THEN [[points_won]] ELSE [[points_lost]] END) as sum1')])

                ->addselect([new \yii\db\Expression('SUM(CASE WHEN [[badminton_match_score.winner_id]]='.$this->id.' THEN [[points_lost]] ELSE [[points_won]] END) as sum2')])

                ->addselect([new \yii\db\Expression('COUNT(badminton_match_score.id) as played')])

                ->leftjoin('badminton_matches M','M.id=match_id')

                ->where('M.team_one='.$this->id.' OR M.team_two='.$this->id.' and M.winner_id is not null')->

                    asArray()->one();


        return $average    =   ($sum_query['sum1']-$sum_query['sum2'])/$sum_query['played'];


}

1 Like

Could this help?

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#selecting-extra-fields