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.

Further information: at the moment I do this by having a public function getProjectsum() in the model with a corresponding public variable projectsum. This means that I can access the info as if it were a variable of the model like this: $model->projectsum, but it doesn't feel yii-ified in that the sql is run every time I access the model variable. — SiggiSmara, Oct 31 '12 at 12:50

score 1 · Answer 1 · answered Oct 31 '12 at 13:27

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.

This might be something, I'll play around a bit and report back (hopefully not too long in the future...) — SiggiSmara, Nov 07 '12 at 11:38

Snivs · Answer 2 · 2012-11-07T15:44:35.803

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;
   }
}

I don't see the real difference of this solution vs what icefront (and I) had come up with although I do like it. It still executes an sql query every time you want to access this information. Which is fine if you are only dealing with one project but creating a summary of costs for all projects and/or searching on the cost value might be difficult. — SiggiSmara, Nov 07 '12 at 11:37
The query is executed only whenever a model is finished loading.. The usage of $projectSum is to have only one database access to load the main information and calculate this intrincate information with it, thus avoiding the need for separate querying, because it is stored within the model, and available for usage without querying again. I just like to know I have all the information I need in my model without needing to do specific querying everytime I need it. Plus, I think the model can be customized to load this information only on certain scenarios or such... — Snivs, Nov 07 '12 at 15:40

score 0 · Answer 3 · answered Oct 31 '12 at 20:34

0

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

answered Oct 31 '12 at 20:34

icefront

174
2
5

Thanks icefront, this is what I'm currently doing :) I was thinking more on the lines of pre-fetching this information like what is done in the relations. – SiggiSmara Nov 07 '12 at 11:33

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

3 Answers3