Yii Framework Forum: CGridView Totals or Summary Row - Yii Framework Forum

Jump to content

  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

CGridView Totals or Summary Row How do you create a custom row for summary data? Rate Topic: ****- 2 Votes

#1 User is offline   luoshiben 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 51
  • Joined: 15-January 09

Posted 02 June 2010 - 06:30 PM

Hi All,

I'm using CGridView to display spreadsheet-like data for a report. For the most part it works swimmingly. However, I seem to be lacking a way to add a summary row for totals. For example, if I had a table with columns "name" and "age" and "weight", and rows of data for each person with their associated values, I might want to have a final row to display the total or average weight or age for all people in my data set. Is this possible? If not natively available in the CGridView widget, can I add a custom data row to the end of the $data returned by my data provider before it gets rendered? Any suggestions or ideas are much appreciated!
0

#2 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 03 June 2010 - 05:11 AM

I've added a method getTotals() to my model, that calculates some total value depending on the current attributes (similar to how the CActiveDataProvider is built in search() ). Then in the grid view i use a footer for one column like this:

array(
    'name'=>'Time',
    'value'=>'sprintf(\'%02s:%02s\',$data->hours,$data->minutes)',
    'type'=>'text',
    'footer'=>$provider->itemCount===0 ? '' : $model->getTotals(),
),

4

#3 User is offline   luoshiben 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 51
  • Joined: 15-January 09

Posted 03 June 2010 - 12:12 PM

View PostMike, on 03 June 2010 - 05:11 AM, said:

I've added a method getTotals() to my model, that calculates some total value depending on the current attributes (similar to how the CActiveDataProvider is built in search() ). Then in the grid view i use a footer for one column like this:

array(
    'name'=>'Time',
    'value'=>'sprintf(\'%02s:%02s\',$data->hours,$data->minutes)',
    'type'=>'text',
    'footer'=>$provider->itemCount===0 ? '' : $model->getTotals(),
),



Thanks, Mike! I've looked at the footer attribute in the docs several times, but was never really sure what it was for and could never find any examples of usage anywhere. This is just what I need. I really appreciate the tip.
0

#4 User is offline   mrwallace 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 12-September 10

Posted 05 December 2010 - 02:49 PM

Anyway to do this with a CArrayDataProvider?
Retreat is no longer an option...
0

#5 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 06 December 2010 - 03:45 AM

How do you create this provider? You should have some sort of model class that provides the provider. That class would be the best place for the method mentioned above.
0

#6 User is offline   mrwallace 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 31
  • Joined: 12-September 10

Posted 06 December 2010 - 04:13 AM

The provider is created through an array retrieved from a SQL query command. Since the data is not retrieved from the site's own database.
Retreat is no longer an option...
0

#7 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 06 December 2010 - 04:18 AM

The question is: Where do you have this code? Like i suggested above i'd wrap this into some sort of model class. It does not necessarily have to extend CModel - but it should contain all the related business logic for reading/writing this data (a.k.a db query code).
0

#8 User is offline   Daniel 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 214
  • Joined: 26-September 09

Posted 20 December 2010 - 11:17 PM

View PostMike, on 03 June 2010 - 05:11 AM, said:

I've added a method getTotals() to my model, that calculates some total value depending on the current attributes (similar to how the CActiveDataProvider is built in search() ). Then in the grid view i use a footer for one column like this:

array(
    'name'=>'Time',
    'value'=>'sprintf(\'%02s:%02s\',$data->hours,$data->minutes)',
    'type'=>'text',
    'footer'=>$provider->itemCount===0 ? '' : $model->getTotals(),
),



This is great and what I am looking for. But, what if I only want the totals for the displayed rows?

For example, I have 50 rows, and the pagination is every 10 rows. If I am in page 2 which showing rows 11-20. How can I get only the totals for rows 11-20? How can I pass the parameter currentPage and rows per page to the model? and how can I limit the calculation (additions) to only involve result rows 11-20?

Thanks a lot.

Daniel
0

#9 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 21 December 2010 - 03:17 AM

You can add another helper to your model (could be a static method) that accepts the data provider as argument. The data property of this object will contain the current page data. You can loop over this data to create the total sum of the items from the current page:

public static function pageTotal($provider)
{
    $total=0;
    foreach($provider->data as $item)
        $total+=$item->someValue;
    return $total;
}

0

#10 User is offline   itmagetan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 90
  • Joined: 25-February 11

Posted 01 March 2011 - 09:01 PM

View PostMike, on 21 December 2010 - 03:17 AM, said:

You can add another helper to your model (could be a static method) that accepts the data provider as argument. The data property of this object will contain the current page data. You can loop over this data to create the total sum of the items from the current page:

public static function pageTotal($provider)
{
    $total=0;
    foreach($provider->data as $item)
        $total+=$item->someValue;
    return $total;
}



I just test it and works fine, but how if i want the total of rows returned by
$model->search()
? not only the total of displayed rows. And one more, how to get the values of attributes returned by this search model. Any example will be much appreciated. Thanks.
0

#11 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 02 March 2011 - 03:18 AM

Well, refactor your model to suit your requirements. Add another method, that calculates (or queries) the total for the current filter settings. I'd probably use a method like getSearchCriteria() that i can use in both, search()/totals(). Whereas search() returns the data provider, totals() uses the same criteria for calculation of the sum.
0

#12 User is offline   itmagetan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 90
  • Joined: 25-February 11

Posted 02 March 2011 - 04:36 AM

Totally a newbie..could you please give an example please :P. I think there are the other need it too. Thanks be4.
0

#13 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,338
  • Joined: 12-October 09
  • Location:Croatia

Posted 02 March 2011 - 08:50 AM

@itmagetan - please do not post same questions on two different threads.

Here is the other thread - http://www.yiiframew...p?/topic/16709-
Find more about me.... btw. Do you know your WAN IP?
0

#14 User is offline   itmagetan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 90
  • Joined: 25-February 11

Posted 02 March 2011 - 09:27 AM

Ouch..Please forgive me for that. It's unawares writing because i need a solution in a short time for my recent project. No more double post again. Thank you for any kind intention. :)
0

#15 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 02 March 2011 - 03:12 PM

*
POPULAR

Ok, i found that this issue maybe requires some deeper understanding of what's going on behind the scenes of AR. Check out this code:

public function getSearchCriteria()
{
    $criteria=new CDbCriteria;

    $criteria->compare('id',$this->id);
    $criteria->compare('name',$this->name);
    // ... more search conditions

    return $criteria;
}

public function search()
{
    return new CActiveDataProvider(get_class($this), array(
        'criteria'=>$this->getSearchCriteria(),
    ));
}

public function totals()
{
    $criteria=$this->getSearchCriteria();
    $criteria->select='SUM(somecolumn)';
    return $this->commandBuilder->createFindCommand($this->getTableSchema(),$criteria)->queryScalar();
}


We've "outsourced" the creation of CDbCriteria into another method getSearchCriteria() because we need the same criteria like in search(), to calculate the sum over some column. The non trivial part is in totals(): Here we use the CDbCommandBuilder to let it create a CDbCommand object with "SELECT SUM(somecolum)" from our search criteria. Then we call queryScalar() because that's all we need.

Now you can use $model->totals() to retrieve the SUM() over some column for use in your datagrid.
6

#16 User is offline   itmagetan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 90
  • Joined: 25-February 11

Posted 02 March 2011 - 05:57 PM

View PostMike, on 02 March 2011 - 03:12 PM, said:

Ok, i found that this issue maybe requires some deeper understanding of what's going on behind the scenes of AR.

Sure Mike, i realize that anyone who decided to use framework for building application needs to have good understanding in OOP and MVC concepts. I've been programming PHP in procedural style in last 4 years, so, this is a big jump for me.

View PostMike, on 02 March 2011 - 03:12 PM, said:

We've "outsourced" the creation of CDbCriteria into another method getSearchCriteria() because we need the same criteria like in search(), to calculate the sum over some column. The non trivial part is in totals(): Here we use the CDbCommandBuilder to let him create a SELECT SUM(somecolum) CDbCommand object from our search criteria. Then we call queryScalar() because that's all we need.

Now you can use $model->totals() to retrieve the SUM() over some column for use in your datagrid.

I don't know which one is more effective related to Yii's rules, but i've found a solution for my problem led by mdomba. Here's the post http://www.yiiframew...__1#entry82860.

Anyway, thank you very much for the enlightenment. I really appreciate it. Go forward Yii.. ;D
0

#17 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 03 March 2011 - 02:21 AM

View Postitmagetan, on 02 March 2011 - 05:57 PM, said:

Sure Mike, i realize that anyone who decided to use framework for building application needs to have good understanding in OOP and MVC concepts. I've been programming PHP in procedural style in last 4 years, so, this is a big jump for me.


Take your time. It takes some practice to learn "thinking in objects". But i know where you're coming from. And i can say: It brings back a lot of fun into programming - at least for me, because i enjoy to create orderliness where before was only mess :)

View Postitmagetan, on 02 March 2011 - 05:57 PM, said:

I don't know which one is more effective related to Yii's rules, but i've found a solution for my problem led by mdomba. Here's the post http://www.yiiframew...__1#entry82860.


mdomba's solution will work, too. But it has an important difference: It loads all records from the search result and calculates the sum in PHP. This might be o.k. for small amounts of data. But it's a no go for huge datasets. The above solution puts the calculation into the DB (where it should be) and only retrieves 1 single number.
0

#18 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,338
  • Joined: 12-October 09
  • Location:Croatia

Posted 03 March 2011 - 02:31 AM

The current solution on the other link ( why do we have two threads talking about the same problem ??? ) can be easily changed to the solution offered here by Mike... as in the getTotal() function the criteria is sent....
Find more about me.... btw. Do you know your WAN IP?
0

#19 User is offline   itmagetan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 90
  • Joined: 25-February 11

Posted 03 March 2011 - 05:07 PM

Yes mdomba, solution for any of my problem is a happiness for me, but a better solution (as delivered by Mike) is a challenge. Thank you for all of you. This has been triggering me to out of my newbie status. ;D
0

#20 User is offline   1sqUAd 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 17-March 11

Posted 17 March 2011 - 09:50 AM

View PostMike, on 02 March 2011 - 03:12 PM, said:

Ok, i found that this issue maybe requires some deeper understanding of what's going on behind the scenes of AR. Check out this code:

public function getSearchCriteria()
{
    $criteria=new CDbCriteria;

    $criteria->compare('id',$this->id);
    $criteria->compare('name',$this->name);
    // ... more search conditions

    return $criteria;
}

public function search()
{
    return new CActiveDataProvider(get_class($this), array(
        'criteria'=>$this->getSearchCriteria(),
    ));
}

public function totals()
{
    $criteria=$this->getSearchCriteria();
    $criteria->select='SUM(somecolumn)';
    return $this->commandBuilder->createFindCommand($this->getTableSchema(),$criteria)->queryScalar();
}


We've "outsourced" the creation of CDbCriteria into another method getSearchCriteria() because we need the same criteria like in search(), to calculate the sum over some column. The non trivial part is in totals(): Here we use the CDbCommandBuilder to let it create a CDbCommand object with "SELECT SUM(somecolum)" from our search criteria. Then we call queryScalar() because that's all we need.

Now you can use $model->totals() to retrieve the SUM() over some column for use in your datagrid.

If i put in getSearchCriteria $criteria->select=array('SUM(colname) as colname'...
And in total i need to get a total sum of aliases colname
totals()$criteria->select='SUM(colname)';
In total i get the same value as in first raw of colname. Can anybody help?
0

Share this topic:


  • (2 Pages)
  • +
  • 1
  • 2
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users