列错误无效 - 必须存在于表中或是带别名的表达式（Yii 1.x）

Question

In a model I have the following logic:

$criteria = new CDbCriteria();
$criteria->select = 'COALESCE(oca.reason, ocd.reason) AS reward_name, SUM(t.points) AS points, COUNT(t.redeemed_code_id) AS totalCount';
$criteria->join = 'LEFT JOIN organisation_classroom_deductions ocd ON ocd.deduction_id = t.order_product_id AND t.type = 1
                   LEFT JOIN organisation_classroom_achievements oca ON oca.achievement_id = t.order_product_id AND t.type = 2
                   LEFT JOIN myuser ON (myuser.user_id = t.myuser_id)';
$criteria->condition = 'myuser.organisation_id ='. user()->data->organisation->organisation_id;
$criteria->group = 't.order_product_id, t.type';

This has the following SQL query -

SELECT COALESCE (oca.reason, ocd.reason) AS reward, SUM(t.points) AS points, COUNT(t.redeemed_code_id) AS totalCount
FROM
`organisation_classroom_redeemed_codes` `t`
  LEFT JOIN organisation_classroom_deductions ocd ON ocd.deduction_id = t.order_product_i AND t.type = 1 LEFT JOIN organisation_classroom_achievements oca ON oca.achievement_id = t.order_product_id AND t.type = 2 LEFT JOIN myuser ON (myuser.user_id = t.myuser_id)
  WHERE myuser.organisation_id = 37383
  GROUP BY t.order_product_id, t.type

This query when run directly in the database works fine and returns the expected data. However the problem is when I run the criteria logic into my CAtiveDataProvider() i get the error.

    return new CActiveDataProvider($this, array(
        'criteria' => $criteria
    ));

Can anyone suggest how to fix this to get it working inconjunction with my CActiveDataProvider?

Answer 1

Specify your select as follows:

$criteria->select = array(
    'COALESCE(oca.reason, ocd.reason) AS reward_name',
    'SUM(t.points) AS points',
    'COUNT(t.redeemed_code_id) AS totalCount'
);

The reason why your code fails is that on line 917 of CActiveFinder (at least in Yii 1.14, which I use) uses the following logic for handling a string select statement:

if(is_string($select))
    $select=explode(',',$select);

That explode fails miserably on your select statement, given that you have commas within the expression. Format your select as an array, as shown above, to avoid this issue.