Convert this query into CDbCriteria format in Yii

Question

I am still working out the whole functionality of CDbCriteria in Yii 1.x.x - I have a fairly complex SQL query that I want to convert into using the CDbCriteria format with queries within a model (if this is possible with a more complex query.

My initial attempt at this..

$criteria = new CDbCriteria;
$criteria->select = array(
    't.classroom_id, title',
    'COALESCE(COUNT(DISTINCT r.redeemed_code_id),0) AS totalRewards',
    'COALESCE(COUNT(DISTINCT ocm.user_id),0) AS totalStudents',
    'COALESCE(SUM(r.points),0) AS totalPoints'
 );

Can anyone suggest the best way to go about this using the following query? Any advice is recommended thanks..

SELECT
  t.classroom_id,
  title,
  COALESCE ( r.classRewards, 0 ) AS totalRewards,
  COALESCE ( r.classPoints, 0) AS totalPoints,
  COALESCE ( COUNT(DISTINCT ocm.user_id), 0 ) AS totalStudents
 FROM
  organisation_classrooms t
     LEFT JOIN ( select crc.classroom_id,
                        COUNT( DISTINCT crc.redeemed_code_id ) AS classRewards,
                        SUM( crc.points ) as classPoints
                    from classroom_redeemed_codes crc
                       JOIN organisation_classrooms t
                          ON crc.classroom_id = t.classroom_id 
                          AND t.organisation_id = 37383
                    where crc.inactive = 0
                      AND ( crc.date_redeemed >= 1393286400
                       OR crc.date_redeemed = 0 )
                    group by crc.classroom_id ) r
        ON t.classroom_id = r.classroom_id

     LEFT OUTER JOIN organisation_classrooms_myusers ocm
        ON t.classroom_id = ocm.classroom_id
 WHERE
  t.organisation_id = 37383
 GROUP BY title
 ORDER BY t.classroom_id ASC
 LIMIT 10

Answer 1

It's not pretty, but you asked for it :D

$criteria = new CDbCriteria();
$criteria->select = '
  t.classroom_id,
  title,
  COALESCE ( r.classRewards, 0 ) AS totalRewards,
  COALESCE ( r.classPoints, 0) AS totalPoints,
  COALESCE ( COUNT(DISTINCT ocm.user_id), 0 ) AS totalStudents';

$criteria->join = '
    LEFT JOIN ( select crc.classroom_id,
                        COUNT( DISTINCT crc.redeemed_code_id ) AS classRewards,
                        SUM( crc.points ) as classPoints
                    from classroom_redeemed_codes crc
                       JOIN organisation_classrooms t
                          ON crc.classroom_id = t.classroom_id 
                          AND t.organisation_id = 37383
                    where crc.inactive = 0
                      AND ( crc.date_redeemed >= 1393286400
                       OR crc.date_redeemed = 0 )
                    group by crc.classroom_id ) r
        ON t.classroom_id = r.classroom_id

     LEFT OUTER JOIN organisation_classrooms_myusers ocm
        ON t.classroom_id = ocm.classroom_id
';

$criteria->group = 'title';
$criteria->order = 't.classroom_id ASC';
$criteria->limit = 10;
$criteria->addCondition('t.organisation_id = :id');
$criteria->params[':id'] = 37383;

then :

// I will assume that model class name is OrganisationClassrooms
$models = OrganisationClassrooms::model()->findAll($criteria);

// or use it with a dataprovider
$dataProvider= new CActiveDataProvider('OrganisationClassrooms' , array(
  'criteria' => $criteria,
))

Answer 2

I suggest to you create stored procedure for that, so you can easily handle this query. eg. -

$sql = Yii::app()->db->createCommand("CALL sp_getstudentdetails(:organisation_id, :date_redeemed)");
$row = $sql->queryAll(array(':organisation_id' => $organisation_id, ':date_redeemed' => date_redeemed));