3

Given the following diagram:

enter image description here

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:

enter image description here

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?

Improve this question

3 Answers 3

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

this is bringing me closer. I think I need this in my memberOrgBalance relation though rather then the dataprovider criteria right? In the dp it can replace the 'on' though. Since the SUM is in the relation I tried adding it there but if using your clause it does not like the syntax, if I change it to t.MemberId=member.MemberId I get an error saying column not found. I will post this as an edit to the question. I appreciate the help.
can you try adding those attributes above to your Donation "criteria" instead of "with" property? see if you can get the data you want first then you can optimize the relation.
if I change the 'on' to 'condition' and move all 3 to criteria I get the same error. If I move just 'group' and 'having' I get the same results as in my original question. With this type of query and as the tables grow it may just be better to try a straight SQL approach and leave AR behind. Do you think that would be a wiser choice rather than trying to make this one work?
If it still isn't working, certainly just go with straight sql (via "createCommand") or at least only operate on the model (Donation) since that is where your data is and use the relations just to get the names from the other tables. While this isn't a terribly complex query, sometimes it is easier to avoid AR, and it's also going to give you better performance. For complex queries I'll sometimes generate the results and use CArrayDataProvider to create the dataProvider.
Btw, you do need the MemberId in the "group" clause. You should be able to use AR, it just seems like the relations here are a bit complicated. That said, it seems like you are close -- and now that I look at it again, you should just be able to remove the "having" clause and in your DP statement add the MemberId as a "condition" -- since the generated sql seems to be right other than the "having" clause.
0

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
Improve this answer

Comments

0

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.

Improve this answer

1 Comment

yeah, sorry if it wasn't more clear -- this is what I was trying to say -- that query is pretty much the same as the output of the one you posted with the error, less the "having" statement. Glad you got it working.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.