Convert this query to CDbCriteria format in Yii

Question

I am still developing all the functionality of CDbCriteria in Yii 1.xx. I have a rather complicated SQL query that I want to convert to CDbCriteria format with queries in the model (if possible with a more complex query.

My initial attempt ...

$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 a better way to do 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

This is not very, 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 you create a stored procedure for this, so that you can easily handle this request. 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));