Yii 1.1: get summary of related model via STAT relation

10 followers

STAT relation tips ΒΆ

typical scenario: invoice, invoice items.

goal: amount summary from invoice items.

I was looking for a better solution for this scenario, even attempted to creat a 'new' relation call 'SUM', however I did not like any of approaches I was trying to take.

finally ... tata ... :

in your Invoice model :

public function relations()
{
    return array(
        'items'=>array(self::HAS_MANY,  'Invoiceitem', 'invoice_id'),
        'itemsTotal'=>array(self::STAT,  'Invoiceitem', 'invoice_id', 'select' => 'SUM(amount)'),
        'itemsCount'=>array(self::STAT, 'Invoiceitem', 'invoice_id'),
    );
}

in your admin view :

<?php $this->widget('zii.widgets.grid.CGridView', array(
    'id'=>'invoice-grid',
    'dataProvider'=>$model->search(),
    'filter'=>$model,
    'columns'=>array(
        //~~~
        array(
           'header'=>'Invoice Total',
            'value'=>'$data->itemsTotal',
        ),
        //~~~
    ),
)); ?>

more in your model:

'itemsMax'=>array(self::STAT,  'Invoiceitem', 'invoice_id', 'select' => 'Max(amount)'),
'itemsAVG'=>array(self::STAT,  'Invoiceitem', 'invoice_id', 'select' => 'AVG(amount)'),

Total 2 comments

#14940 report it
rootbear at 2013/09/23 10:13am
thanks

thanks @trond, you could actually try out more as long as SQL allows. eg.

SUM(unit_price * qty)
#14867 report it
trond at 2013/09/17 03:31am
Very neat - thanks for sharing

I'll try this out. I have previously done something like this, but I think your solution is more elegant:

public function getTotalDuration($id)
{
    $connection = Yii::app()->db;
    $command = $connection->createCommand('SELECT SUM(duration) FROM tbl_trainingsession_item where trainingsession_id = ' . $id);
    $minutes = $command->queryScalar();
    $hours = floor($minutes / 60);
    $minutes = $minutes % 60;
    if ($hours > 0) {
        return sprintf("%d:%'02d", $hours, $minutes);
    } else {
        return $minutes . ' min';
    }
}

I'll be interested to see if there is any difference in performance.

Leave a comment

Please to leave your comment.

Write new article
  • Written by: rootbear
  • Updated by: YiiJeka
  • Category: How-tos
  • Yii Version: 1.1
  • Votes: +6 / -1
  • Viewed: 6,995 times
  • Created on: Sep 15, 2013
  • Last updated: Apr 22, 2014
  • Tags: relation tips