列错误无效 - 必须存在于表中或是带别名的表达式(Yii 1.x)
问题描述:
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?
答
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.