CODEWITHSUNDEEP
phpmysqlyii
Denis Kulagin
Denis Kulagin

Reputation: 8917

Yii: computing aggregate by date

I have a simple model in Yii 1.1:

Expense

amount: int
date: string

How do I compute an aggregate by date using build-in Yii capabilities?

In other words I would like to get a result of the following query using Yii:

SELECT
  date, SUM(amount) AS dateAmount

FROM
  tbl_expense

GROUP BY
  date

ORDER BY
  date DESC

Upvotes: 0

Views: 192

Answers (3)

venoel
venoel

Reputation: 458

You can use criteria

$criteria = new CDbCriteria();
$criteria->select = 'date, SUM(amount) dateAmount';
$criteria->group = 'date';


$model=Expense::model()->find($criteria);

AND add property dateAmount to the model class.

class SomeClass extends CActiveRecord {
  public $dateAmount;
  ...
}

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

You could use criteria

$criteria = new CDbCriteria();
$criteria->select = 'date, SUM(amount) dateAmount';
$criteria->group = 'date';


$model=Expense::model()->find($criteria);

Upvotes: 1

rakesh mallesh
rakesh mallesh

Reputation: 178

I have explored 2 ways for executing query,

First:

$list= Yii::app()->db->createCommand('SELECT date, SUM(amount) AS dateAmount
FROM tbl_expense
GROUP BY date
ORDER BY date DESC')->queryAll();
var_dump($list->getData());

Second: (Normally used to provide dataprovider to Gridview and Listview)

$select = "SELECT date, SUM(amount) AS dateAmount
FROM tbl_expense
GROUP BY date
ORDER BY date DESC";

$dataProvider=new CSqlDataProvider($select,
array(
    'pagination'=>false,
    'params'=>$sqlParam
   )
);
var_dump($dataProvider->getData());

Upvotes: 3

Related Questions