help with SQL query in Yii

Question

Given the following diagram:

With the code below I have the Donations grouped for each organization now I am trying to calculate the total amount a given member has donated to a given organization.

Something like:

With this code it correctly groups that organizations as I need but the problem I have here is that for the 'Amount Donated to Organization' column all values equal the total of the Organization with the highest Id. Therefore all rows in that column are showing $90

Yii Code:

// member view
<?php   
     $dataProvider=new CActiveDataProvider(Donation::model(), array(
        'criteria'=>array(
            'with' => array(
                'member' => array(
                    'on'=>'member.MemberId='.$model->MemberId, 
                    'group' => 't.MemberId, t.OrganizationId',
                    'joinType'=>'INNER JOIN',
                    ),
                ),
        'together'=> true,
        ),  
    ));
    $this->widget('zii.widgets.grid.CGridView', array(
        'dataProvider'=>$dataProvider,      
        'columns' => array(
            array(
               'name'=>'OrganizationId',
               'value' => '$data->OrganizationId',
            ), 
            array(
               'name'=>'Amount',
               'value' => '$data->memberOrgBalance;',
            ), 
        ),
    )); 
?>


// member model

'memberOrgBalance' => array(self::STAT, 'Donation', 'MemberId', 
       'select'=>'MemberId, OrganizationId, SUM(Amount)', 
       'group' => 'OrganizationId'),

// donation model

'member' => array(self::BELONGS_TO, 'Member', 'MemberId'), 

EDIT: See also response to LDG

Using the advice from LDG I tried adding 'having' to my dataprovider, when that did not seem to affect the query I tried to add it to the relation memberOrgBalance where I am trying to pull the data. This seems to affect the query but it is still not right. I switched to:

'memberOrgBalance' => array(self::STAT, 'Donation', 'MemberId', 
        'select'=>'MemberId, OrganizationId, SUM(Amount)', 
        'group' => 'OrganizationId', 
        'having'=> 'MemberId=member.MemberId',
        ),

which gives this error:

   CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: 
Column not found: 1054 Unknown column 'member.MemberId' in 'having clause'. 
The SQL statement executed was: SELECT `MemberId ` AS `c`, MemberId, OrganizationId, 
SUM(Amount) AS `s` FROM `donation` `t` WHERE (`t`.`MemberId `='2') 
GROUP BY `MemberId `, OrganizationId HAVING (MemberId=member.MemberId)

This makes no sense since from the donation table I have the relation defined as originally posted above. The query seems to be going the direction needed to get the SUM per organization as I want though. Any other ideas?

Answer 1

Ok after running around in circles with this someone was able to push me over the top to a solution on Yii forums.

The end result is

$criteria->condition='member.MemberId="'.$model->MemberId.'"';
$criteria->with='member';
$criteria->select='MemberId,OrganizationId,sum(Amount) as Amount';
$criteria->group='t.MemberId,OrganizationId';
$dataProvider=new CActiveDataProvider(Donations::model(),
    array(
        'criteria'=>$criteria,

Thanks ldg for the help with this.

Answer 2

This is the SQL query needed..

select donation_org_id , sum(donation_amount) as donated_amount, count(d.donation_id) as members_count
from donations d
group by d.donation_org_id

Answer 3

If I understand what you are trying to it would seem like you need to add a "having" attribute, something like

'on'=>'member.MemberId = t.MemberId', 
'group' => 't.MemberId, t.OrganizationId',
'having'=> 't.MemberId=' . $model->MemberId