using Active record with SUM() AND CASE

i am converting the following query using yii2 active record


    (SUM(CASE WHEN THEN BMS.points_won ELSE BMS.points_lost END)-

    SUM(CASE WHEN THEN BMS.points_lost ELSE BMS.points_won END))


     badminton_match_score BMS 

     JOIN badminton_matches M ON ( where OR 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( as played')])

                ->leftjoin('badminton_matches M','')

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


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


1 Like

Could this help?