Complicated Sql Statement for CDbCriteria to be used in CActiveDataProvider

I have a complicated sql statement and I am having trouble in interpreting it for CDbCriteria.

The result will be passed in a CActiveDataProvider.

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

Basically I can use join but I don’t know when it comes to joining multiple selects instead of tables.

Can someone help me. TIA

You can use CArrayDataProvider instead.

In manner:




$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)';

Thanks for the help though. :)