I have a grid that will calculate the balance given from each user to a given charity and group the balance per charity in the grid. This is my dp for that.
<?php
$criteria->with='acc';
$criteria->condition='acc.AccountId='.Yii::app()->user->id.'
AND t.CharityId='.$model->CharityId;
$criteria->select='AccountId,CharityId,sum(Amount) as Amount';
$criteria->group='t.AccountId,CharityId';
$dataProvider=new CActiveDataProvider(Trans::model(), array(
'criteria'=>$criteria,
));
?>
Now I want to create a query that will give me the same balance for a given charity outside of the grid.
<?php
public function getActiveBalance ()
{
$balance = Yii::app()->db->createCommand()
->select('AccountId,CharityId,SUM(Amount) as Amount')
->from('trans')
->where('AccountId=:id, CharityId=:cha', array(':id'=>Yii::app()->user->id, ':cha'=>$this->CharityId))
->group('AccountId,CharityId')
->query();
return $balance;
}
?>
With this I get this error
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near ' CharityId='5'
GROUP BY `AccountId`, `CharityId`' at line 3. The SQL statement executed was: SELECT AccountId,CharityId,SUM(Amount) as Amount
FROM `trans`
WHERE AccountId=:id, CharityId=:cha
GROUP BY `AccountId`, `CharityId`. Bound with :id=1, :cha=5
I am not sure the cause of this error though. Any ideas?