Convert this query into CDbCriteria format in Yii

Yii 1.1 General Discussion
1

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.

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

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'

 );







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
2

I think you should first add relations to the model like this:


	

public function relations()

{

	return array(

		'classroom_redeemed_codes' => array(self::BELONGS_TO, 'ClassRoomRedeemedCodes', 'classroomredeemedcode_id'),

	);

}

Then you could use "alias", "with", "condition" and "group". For example:


$criteria = new CDbCriteria;

$criteria->alias='t';

$criteria->with = array('classroom_redeemed_codes'=>array('alias'=>'crc'));

$criteria->select = array(

    't.classroom_id, t.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'

 );

$criteria->condition = "t.organisation_id = 37383";

$criteria->group = 't.title',
3

Thanks i’ll give that a whirl :)

4

here you have more information on cdbcriteria

http://www.yiiframework.com/doc/api/1.1/CDbCriteria

http://www.bsourcecode.com/2013/06/cdbcriteria-in-yii/#With

You could also use "join" instead of "with"