retrieving a sum of two columns from two related models in yii

Question

I'm fairly new working with yii but have plenty of experience with php and sql. I'm hoping that someone more experienced with yii can point me in the right direction. I have two models, Project and Costs, which are related to each other in a many to many relationship through a project_cost table. The reason for this is that costs can be shared between projects. In the project_cost table there is an additional column containing how much of the cost is assigned to the specific project.

So the Project model relations looks like this which is working perfectly fine for getting all the details:

class Project extends CActiveRecord
{

    /**
    * @return array relational rules.
    */
    public function relations()
    {
    return array(
        'projectcost'   => array(self::HAS_MANY,   'ProjectCost',    'project_id'),
        'cost'      => array(self::HAS_MANY, 'Cost', array('cost_id'=>'id'),'through'=>'projectcost'),
        //i.e. a many to many relation of cost through the projectcost model

        );
    }
    ...
}

In the cost model there is a column named Value and in the project_cost table there is column name Percent. It is easy to construct a function that contains a sql query that gives me the sum for the project cost like this:

select sum(project_cost.Percent*cost.Value) 
from project_cost join cost on project_cost.cost_id=cost.id 
where project_cost.project_id=1     

but is there a way to do the same via relations in yii? I know about STAT relations but am unclear on how they can be applied in this case as most of what I have read so far indicates that relations work best if there are only two models in the relationship.

Answer 1

You may be able to create a scope to grab this data, creating a method in your Project model something like:

public function withAdjustedCost()
{
    $this->getDbCriteria()->mergeWith(array(
        'with'=>array(
            'projectcost',
            'cost',
        ),
        'select'=>'*, sum(projectcost.Percent*cost.Value) as adjustedCost',
    ));

    return $this;
}

You should then be able to use this when you grab your project models;

$models = Project::model->withAdjustedCost()->findAll();

I've not tested it, and you might need to tinker with it a little, or might need to define $adjustedCost as a property of your model in order to access it (then again, maybe not)... anyway, might be a direction to go.

Answer 2

You might want to take a look at the CDbCriteria class, which can allow you to construct a query using Yii framework, thus 'correctly'.

Also, I think you could use the CActiveRecord->getRelated() method to pull out all the ProjectCost models in an array, then loop using foreach in order to build up the sum you want. In your Project model, you could create a method pretty much like this:

class Project extends CActiveRecord
{
  ...
  /**
  * @return the sum of all weighed Costs corresponding to this Project.
  */
  public function getTotalCost()
  {
      $projectCosts = $this->getRelated('projectcost'); // fetches array of ProjectCost models, which you can iterate over
      $sum = 0;
      foreach($projectCosts as $projectCost)
      {
          $sum += $projectCost->Percent * $projectcost->getRelated('cost')->Value;
      }
      return $sum;
  }
  ...
}

This, relies on the following ideas:

$projectcost->getRelated('cost')->Value;

Your ProjectCost model has a relation like this:

'cost'   => array(self::HAS_ONE,   'Cost',    'cost_id'), 

Which allows getRelated() to fetch a related Cost model, whenever you require access to it.

So you could basically end up simply using $model->getTotalCost() to get the sum of costs you expressed in your query, but actually built within the model, which I consider proper MVC, rather than letting the Database do calculations for you using a query.

Edit: getRelated() has proven to be a real friend when working with related records, it fetches an array of records or a single record, depending on the nature of the relationship it works with.

As of your seeming concern about database access you could also take a look at CActiveRecord->afterFind() which you can overload in order to calculate the project sum and set it to your public variable only once right after a model is loaded. so you would have your code like:

public $projectSum = 0;
public function afterFind(){
   //Fetch array of ProjectCost models, which you can iterate over
   foreach($this->getRelated('projectcost') as $projectCost)
   {
     $this->projectSum += $projectCost->Percent * $projectcost->getRelated('cost')->Value;
   }
}

Answer 3

Simple and effective:

$sum = Yii::app()->db->createCommand("
    select sum(project_cost.Percent*cost.Value)
    from project_cost
    join cost on project_cost.cost_id=cost.id
    where project_cost.project_id=1
")->queryScalar();

$sum === false if something's wrong