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