Using a SQL query

Using the diagram:

This query …

SELECT

    MemberId,

    OrganizationId,

    SUM(Amount) AS `Amount Donated to Organization`,

    COUNT(Amount) AS `Number of Donations`

FROM

    Donations

GROUP BY

    MemberId,

    OrganizationId

;

will give me what I need …

In my donation model I want to use this info in CGridView. This is where I need the help.

I tried creating a function but all it does is give me a count on the different organizations.

public function getBalanceAmount () {

  $dbCommand = Yii::app()->db->createCommand(

    "SELECT

      MemberId,

      OrganizationId,

      SUM(Amount) 

    FROM

      Donations

    GROUP BY

      MemberId,

      OrganizationId;"

  )->execute();

  return $dbCommand;

}

So how can I directly use this sql and then call it into my gridview?

u can use this code in your donation controller

public function getBalanceAmount()

{

$criteria->select='MemberId,OrganizationId,sum(Amount) as Amount';

$criteria->group='MemberId,OrganizedId';

$dataProvider=new CActiveDataProvider('BatchReport',array(

			'criteria'=>$criteria,

			'pagination'=>array(

				'pageSize'=>30,

			),

		));

}

and u this in your gridview

$this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'batch-report-grid',

	'dataProvider'=>$dataProvider,

	'columns'=>array(

		'MemberId',

		'OrganizationId',

		array(

			'name'=>'Amount Donated to Organization',

			'value'=>'$data->Amount',

		),

));

note: use $criteria->addCondition if your query use where like select * where memberId = 1

hope this will help you

Thanks with that info I was able to get the results that I need. That is a huge help.

I am still curious how to use Yii::app()->db->createCommand properly too if anyone else can help with that?

You used the execute() method in your query… check the documentation for this method - http://www.yiiframework.com/doc/api/1.1/CDbCommand#execute-detail

Spoiler: it returns just the number of rows affected… so you need to use a different method like query(), queryAll()