CODEWITHSUNDEEP
phpsqlactiverecordyiiaggregate-functions
Turgs
Turgs

Reputation: 1799

How can Yii Relational Query's provide an aggregation broken-down by category?

I have a Person that can receive many types of Income. I want to be able to return the SUM income earned, broken down by each Income Type. 

+---------------+-----------------+
| INCOME TYPE   | SUM AMOUNT      |
+---------------+-----------------+
| Salary        | $934.33         |
| Gambling      |  $27.99         |
| Tips          | $584.00         |
+---------------+-----------------+

I've been able to get a self::STAT relational query to provide the overall Income received ($1546.32), but not broken-down by Income Type.

As a result, I'm trying to do it as a self::HAS_MANY relational query within the Person model, like so:

  public function relations() {
     return array(
        'incomes_total' => array(
           self::HAS_MANY,
           'Income',
           'person_id',
           'select' => array('income_type_id', 'SUM(amount) AS total'),
           'group' => "income_type_id"
     ));
  }

This results in the following SQL being generated:

SELECT
  "incomes_total"."income_type_id" AS "t1_c5"
, SUM(amount) AS total
, "incomes_total"."id" AS "t1_c0"
FROM "stwd_income" "incomes_total"
WHERE ("incomes_total"."person_id"=:ypl0)
GROUP BY income_type_id

This gets very close to the query needed.

What I can't figure out is why the line , "incomes_total"."id" AS "t1_c0" has been added in there. I don't want that. It's causing a CDbException error:

CDbCommand failed to execute the SQL statement: 
SQLSTATE[42803]: Grouping error: 7 ERROR: column "incomes_total.id" must 
appear in the GROUP BY clause or be used in an aggregate function.

How can I achieve the type of query that I need to?

UPDATE 1: The difference between Active Record and custom SQL queries is starting to become clear, and that Active Record may not be appropriate for such a task.

Upvotes: 2

Views: 879

Answers (1)

Haensel
Haensel

Reputation: 335

Personally I wouldn't use ActiveRecord for anything else than very basic queries and CRUD. Even if STAT relations exist they are better used for simple counts or sums. And a HAS_MANY relationship is wrong in any way as you are not expecting an array of models, are you? You only want a simple array of ids and sums, so in your case I would simply create a method in the Person model like this

private $_salaryInfo;

public function getSalaryInfo()
{
    //cache the value if possible to save unnecessary queries
    if(isset($this->_salaryInfo))
        return $this->_salaryInfo;

    //query database
    $command = Yii::app()->db->createCommand();
    $command->select('income_type_id, SUM(amount)')->where('person_id = '.$this->id)->groupBy('income_type_id');
    return $this->_salaryInfo = $command->queryAll();
}

You should now be able to do

foreach($person->salaryInfo as $type_id => $sum)

Upvotes: 1

Related Questions