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

#21 User is offline   yiifan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 27
  • Joined: 16-November 10

Posted 08 June 2011 - 01:40 AM

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

    return $this->commandBuilder->createFindCommand($this->getTableSchema(),$criteria)->queryScalar();



My search() method takes an argument that is a list of values of a relational table attribute; this is used when building the criteria. Given that my criteria uses a condition that requires a relational table, am unable to use createFindCommand as it creates a select command only on a single table. How should I handle such a scenario? Should I rebuild the entire command, including joins, separately without reusing the getSearchCriteria() code to get the totals?

Thank you.
0

#22 User is offline   Mike 

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

Posted 09 June 2011 - 12:52 AM

The given example was taylored to the given use case. If you need something else, you can build any command you want in totals(). You can also save the arguments to search() in another private variable and access them from totals(), in case you need them for calculating the sum.
0

#23 User is offline   yiifan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 27
  • Joined: 16-November 10

Posted 09 June 2011 - 01:12 AM

View PostMike, on 09 June 2011 - 12:52 AM, said:

The given example was taylored to the given use case. If you need something else, you can build any command you want in totals(). You can also save the arguments to search() in another private variable and access them from totals(), in case you need them for calculating the sum.


I guess I should have phrased the question better. Was wondering if it was possible to to re-use the getSearchCriteria() method for both search() and totals(), and build a query in totals() using the CDbCriteria object returned by getSearchCriteria() when this object has a relational table condition. createFindCommand can run a select command only on one table but the criteria object uses another relational table, so I can't use createFindCommand(). I was basically looking for an alternative to this.
[Passing/storing the arguments is not an issue.]
0

#24 User is offline   Mike 

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

Posted 09 June 2011 - 01:28 PM

After looking at my example code again i wonder, if anyone has tested it at all :). I guess it doesn't work, because when the dataprovider fetches the data, it applies a limit to the search criteria. So when calling totals() the same limit will still be set in the criteria. To make it work, we would have to unset the limit in the totals() method first.

Anyway ...

@yiifan: I think in this case it's not so easy. You could study CActiveFinder. But you will find that it's very hard to retrieve the full SQL there. So you can't create your custom command to query for the SUM() over some column.
0

#25 User is offline   chriscao 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 43
  • Joined: 03-February 10

Posted 02 November 2011 - 01:51 AM

It's great! Many Thanks
0

#26 User is offline   Fercho 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 10-February 12

Posted 01 May 2012 - 04:12 PM

View Postmrwallace, on 05 December 2010 - 02:49 PM, said:

Anyway to do this with a CArrayDataProvider?



View PostMike, on 06 December 2010 - 04:18 AM, said:

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).


In my case im using a component that do the query with commandController and also return an array, so thats why im using also the CArrayDataProvider and id like to know how to get subtotal with pagination.

Anyone knows how to solve it?
0

#27 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 3,034
  • Joined: 16-February 11
  • Location:Japan

Posted 05 October 2012 - 05:38 AM

It's a little old topic, but I just want to say thanks to Mike for this wonderful solution.
It helped me a lot.

And I want to share my bit of experience.
The following is the abbreviated code of what I did to satisfy my specific needs.

class MyModel extends CActiveRecord
{
	...
	public $amount_sum;		// virtual attribute for sum(amount)
	public $price_sum;		// virtual attribute for sum(price)
	...

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

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

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

	public function fetchSums()
	{
		$criteria = $this->getSearchCriteria();
		$criteria->select = array(
			'SUM(t.amount) as amount_sum',
			'SUM(t.price) as price_sum'
		);
		$ret = MyModel::model()->find($criteria);
		if ($ret)
		{
			$this->amount_sum = $ret->amount_sum;
			$this->price_sum = $ret->price_sum;
		}
		else
		{
			$this->amount_sum = 0;
			$this->price_sum = 0;
		}
	}
}


1) I introduced 2 virtual attributes for the totals.

2) I called fetchSums() method from search() method.

3) I used CActiveRecord::find() instead of CDbCommand::queryScalar() or CDbCommand::queryRow().
CDbCommandBuilder::createFindCommand() didn't work when I specified a condition on the related table. It's maybe because createFindCommand "creates a SELECT command for a single table", as the class reference says.

http://www.yiiframew...dCommand-detail

4) About pagination ... I didn't find anything wrong so far.
The calculation of sum works on the total available rows, not limited to the rows in the current page.
As far as I understand, 'OFFSET' and 'LIMIT' is (and should be) added to the criteria later by the CGridView or CListView, not by search() method.

Anyway, it's working great. Thanks a lot, Mike. :D
0

#28 User is offline   Sivanthi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 14-February 13
  • Location:Nagercoil, Kanya Kumari, Tamilnadu. India

Posted 13 March 2013 - 01:29 AM

View Post1sqUAd, on 17 March 2011 - 09:50 AM, said:

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?



example i have created for another one project. hope it will help to solve this problem.



in cgridview

'columns'=>array(
'patientname',
array(
'name'=>'payment',
'type'=>'text',
'footer'=>$model->getTotals($model->search()->getKeys()),
),
'mode',
'employeerel.emp_name',
'recdate',
array(
'class'=>'bootstrap.widgets.TbButtonColumn',
),

in model

public function getTotals($ids)
{
$ids = implode(",",$ids);

$connection=Yii::app()->db;
$command=$connection->createCommand("SELECT SUM(payment)
FROM `payment` where id in ($ids)");
return "Total Rs: ".$amount = $command->queryScalar();
}
0

#29 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 14 March 2013 - 01:49 AM

This is really interesting thread to follow.

There arises two scenario.

1.Getting total of a field in a single page.

2.Getting total for a particular field in all the pages.

1.The first scenario is well explained by Mike.

I have a model Wage.

I have a field Hours.

In the model,
Wage.php
public function fetchTotalHours($wages)
{
	$hours=0;
	foreach($wages as $wage)
	    $hours+=$wage->hours;
        return $hours;   
}


We can declare the column for the attribute in the following way.
array(
	'name'=>'hours',
	'footer'=>"Total: ".$model()->fetchTotalHours($model->search()->data),
		),


2.In the second scenario, we can modify the model method in the following way.
public function fetchTotalHours($criteria)
{    
	$wages=self::model()->findAll($criteria);
	$hours=0;
	foreach($wages as $wage)
		$hours+=$wage->hours;
	return $hours;
}

In admin.php, we can declare the column in the following way.

array(
	'name'=>'hours',
	'footer'=>"Total: ".$model->fetchTotalHours($model->search()->criteria),
		),

Now we can get total of all the records, spanning across all the pages.
0

#30 User is offline   tooba 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 04-October 13

Posted 06 November 2013 - 06:22 AM

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(),
),




Hey Elite plz help me... I am getting error in this line
'footer'=>dataProvider->itemCount===0 ? '' : $model->getTotals(),

Attached File(s)


0

#31 User is offline   tooba 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 04-October 13

Posted 12 November 2013 - 11:28 PM

View Postseenivasan, on 14 March 2013 - 01:49 AM, said:

This is really interesting thread to follow.

There arises two scenario.

1.Getting total of a field in a single page.

2.Getting total for a particular field in all the pages.

1.The first scenario is well explained by Mike.

I have a model Wage.

I have a field Hours.

In the model,
Wage.php
public function fetchTotalHours($wages)
{
	$hours=0;
	foreach($wages as $wage)
	    $hours+=$wage->hours;
        return $hours;   
}


We can declare the column for the attribute in the following way.
array(
	'name'=>'hours',
	'footer'=>"Total: ".$model()->fetchTotalHours($model->search()->data),
		),


2.In the second scenario, we can modify the model method in the following way.
public function fetchTotalHours($criteria)
{    
	$wages=self::model()->findAll($criteria);
	$hours=0;
	foreach($wages as $wage)
		$hours+=$wage->hours;
	return $hours;
}

In admin.php, we can declare the column in the following way.

array(
	'name'=>'hours',
	'footer'=>"Total: ".$model->fetchTotalHours($model->search()->criteria),
		),

Now we can get total of all the records, spanning across all the pages.


How to access this total out of footer in yii??
0

#32 User is offline   Vi Quang Hòa 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 06-June 09

Posted 18 December 2013 - 10:55 PM

I have done :D :D

Attached File(s)


0

#33 User is offline   paragxviii 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 26-June 14
  • Location:Pune,India

Posted 03 July 2014 - 07:01 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.


Thanks mate .. Bang on target.. Great solution.. :)
Cheers
There are 10 types of people in this world.
Those who understand binary and those who don't
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