php - Convert this query into CDbCriteria format in Yii

455

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
825

Answer

Solution:

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,
))
139

Answer

Solution:

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

People are also looking for solutions to the problem: javascript - Check input types and skip input 'password' field while click SAVE

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.