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'];
}