$rawData=Yii::app()->db->createCommand('
SELECT count(idstudent_profile) as TotalStud, name FROM sections left join
(SELECT * FROM student s JOIN (SELECT * FROM student_profile sp
JOIN (SELECT * FROM school_year
where status="current") sy
USING (idschool_year)) stud_sy USING(idstudent)) stud_sy_studinfo USING (idsection)
GROUP BY name
')->queryAll();
$dataProvider=new CArrayDataProvider($rawData, array(
'id'=>'user',
'sort'=>array(
'attributes'=>array(
'TotalStud', 'name'
),
),
'pagination'=>array(
'pageSize'=>10,
),
));
It seems that the complicated joins can all be done inside the join of CDbCriteria.
Like this:
$criteria->join='left join (SELECT * FROM student s JOIN (SELECT * FROM student_profile sp JOIN (SELECT * FROM where status="current") sy USING (idschool_year)) stud_sy USING(idstudent)) stud_sy_studinfo USING (idsection)';