CODEWITHSUNDEEP
phpmysqlyii
Zabs
Zabs

Reputation: 14142

Using CASE in Yii CDbCriteria statement

Can anyone suggest why this CASE statement doesn't work within my Yii CDbCriteria statement?

$criteria = new CDbCriteria;
$criteria->select = array('t.*',
        '(CASE
            WHEN t.type = 3 THEN "Quick Point"
            WHEN t.type = 2 THEN positive.reason
            WHEN t.type = 1 THEN deduction.reason
            END AS reason)');

I get the following error:-

Active record "OrganisationClassroomRedeemedCodes" is trying to select an invalid column "(CASE 
WHEN t.type = 3 THEN "Quick Point"
WHEN t.type = 2 THEN positive.reason 
WHEN t.type = 1 THEN deduction.reason 
END AS reason)". Note, the column must exist in the table or be an expression with alias.

I am essentially trying to do this query using the CDbCriteria builder

SELECT `t`.*,
CASE
 WHEN t.type = '3' THEN 'Quick Point' 
 WHEN t.type = '2' THEN positive.reason
 WHEN t.type = '1' THEN deduction.reason
  END AS reason
FROM
`organisation_classroom_redeemed_codes` `t`
 LEFT OUTER JOIN `organisation_classroom_achievements` `positive` ON (
`positive`.`achievement_id` = `t`.`order_product_id`
) 
LEFT OUTER JOIN `organisation_classroom_deductions` `deduction` ON (
`deduction`.`deduction_id` = `t`.`order_product_id` 
) 
WHERE (t.myuser_id = 12345)
ORDER BY `t`.`date_redeemed` DESC

Upvotes: 0

Views: 957

Answers (1)

Zabs
Zabs

Reputation: 14142

If it helps anyone else... this is the correct way

    $criteria->select = array('t.*',
        '(CASE
            WHEN t.type = 1 THEN deduction.reason
            WHEN t.type = 2 THEN positive.reason
            WHEN t.type = 3 THEN "custom"
            END) AS reason',           
    );

Upvotes: 1

Related Questions