1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading ... Loading ...

Cakephp model–sum, avg and etc on same model

If you need to sum up or average a column on a table via bindModel. this is what you are looking for.
The table looks like this

//each report is connected to same session report via session_id
CREATE TABLE IF NOT EXISTS `gather_reports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(64) NOT NULL DEFAULT '',
  `type` enum('Category','Store') NOT NULL DEFAULT 'Category',
  `name` varchar(128) NOT NULL DEFAULT '',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `importedId` text NOT NULL,
  `total` int(11) NOT NULL DEFAULT '0',
  `imported` int(11) NOT NULL DEFAULT '0',
  `duration` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
)

in your controller

$this->GatherReport->bindModel(
    array('hasOne'=>array(
	'GatherTotal'=>array(
        	'className'=>'GatherReport',
		'foreignKey'=>'id',
		'fields'=>'SUM(GatherTotal.total) as total'
	),
	'GatherImported'=>array(
		'className'=>'GatherReport',
		'foreignKey'=>'id',
		'fields'=>'SUM(GatherImported.imported) as imported'
	),
	'GatherTime'=>array(
		'className'=>'GatherReport',
		'foreignKey'=>'id',
		'fields'=>'SUM(GatherTime.duration) as duration'
	)
)),false);
$this->paginate['GatherReport']['group'] = 'GatherReport.session_id';
$data = $this->paginate('GatherReport');
Share:
  • Print
  • Digg
  • del.icio.us
  • Facebook
  • Google Bookmarks
  • MySpace
  • Reddit
  • RSS
  • Slashdot
  • StumbleUpon
  • Twitter
  • Yahoo! Bookmarks
Share your opinion! Post your thoughts.