HAS_MANY relation with generating SUM

Yii 1.1 General Discussion
1

Hello, once again I’m hoping for your help!

I have to tables:

MAINTABLE

id (INT)

…

SECONDTABLE

id (INT)

maintable_id (INT)

value (INT)

and therefore 2 models Maintable and Secondtable.

What I want is to get a find-all-query for the maintable, extended by an additional attribute sumValue which is the sum of all connected records of secondtable.

I tried it with this code:

Maintable-model:




public function relations() {

	return array(

    	'values' => array(self::HAS_MANY, 'Secondtable', 'maintable_id',

                    'select'=> 'SUM(value) AS sumValue','group' => '{{maintable}}.id'),

	);

}

Secondtable-model:




public $sumValue;

In the maintable-controller I do:




$dataProvider=new CActiveDataProvider(Maintable::model()->with('values'), array(...));

expecting that I could use the sumValue later on in a view like this:




<?php echo $data->id; ?>	

<?php echo $data->values->sumValue; ?>

but unfortunately I get the error:

"Unknown column ‘yii_maintable.id’ in ‘group statement’.

My Mysql-Logfiles show me not an left outer join as I was expecting, but several seperated

SQL-Statements, one of them:




SELECT SUM(value) AS sumValues, `yii_secondtable`.`id` AS `t1_c0` FROM `yii_secondtable` `yii_secondtable`  WHERE (`yii_secondtable`.`id`='51') GROUP BY yii_maintable.id

which explains the error message.

I tried this as well:




public function relations() {

	return array(

		'values' => array(self::HAS_MANY, 'Secondtable', 'maintable_id', 

                            'select'=> 'SUM(value) AS sumValue','group' => '{{secondtable}}.maintable_id'),

	);

}

which makes the error message disappear but will not show all records of maintable, but only those with an connected entry in secondtable. Seems not to be an LEFT OUTER JOIN.

What can I do?

EDIT:

meanwhile I found out, that my second version DOES execute a LEFT OUTER JOIN but groups the records by {{secondtable}}.maintable_id, which might be "null". So only the maintable records with secondtable records plus one null-secondtable record are displayed.

How can I solve this? Or do I have to re-think the general approach?

EDIT:

I think the problem was somewhere else:

I had two CActiveDataProviders which where referring to the same tables and relations but with different conditions. The first one works fine, the second one produces the error message above (first variant of relation).

I suppose that there was not created a complete LEFT OUTER JOIN Statement for the second dataprovider which might be connected with http://code.google.com/p/yii/issues/detail?id=1195 (?)

I have no idea how to force Yii to build the complete Statement at the second dataprovider too.

So I’ll try to solve this without using dataprovider…

2

Hi,

this post is bit old, but I have the solution for it:




return array(

	'country' => array(self::BELONGS_TO, 'Country', 'country_id'),

	'commissions' => array(self::HAS_MANY, 'Commission', 'affiliate_id',

		'condition'=>'commissions.approved=1',

		'order'=>'commissions.sale_date'),

	'commissionSum' => array(self::STAT, 'Commission', 'affiliate_id',

		'select'=> 'SUM(amount)',

		'condition'=>'approved=1'),

	'commissionCount' => array(self::STAT, 'Commission', 'affiliate_id',

		'condition'=>'approved=1'),

);

then, to get the total amount, you can use:


$model->commissionSum

;)

3

Thanks for your code. Great.

4

It’s very good!