I have a table where i store the userId, and points. Everytime a user uses a transaction, they will be awarded points and these points are stored in UserPoints. It will post new rows for each transaction.
id | userId | points
1 2 38
2 18 50
3 2 12
4 10 13
5 15 15
like the table above, userID 2 has multiple lines, i want to add all the value that userId 2 have. i want to put the calculation inside the model MUserTotal inside the variable totalPointsValue().
Here is what i have so far
public function totalPointsValue() {
user = $this->userId;
}
What you need is statical query.
Basically you create a relation ship on the user Class to the UserPoints of type STAT, and specify the criteria:
<?php
class User extends CActiveRecord
{
public function relations()
{
return array(
'points'=>array(
self::STAT, // Declare this relation as statical
'user_points', // The table where you are storing the points
'userId', // The foreign key on that table
'select' => "SUM(points)", // make it SUM instead of default COUNT
'group' => "userId" // Group by this field when adding points
),
);
}
}
After that you can use the relationship as you would use any other:
<?php
$user = User::model()->findbyPk(1);
echo $user->points;
or if you need to create the model MUserTotal ... just create a view that contains the userId and sum of his points, and create the model for this view ... the query will be some thing like
select user_id, sum(points) from user_points group by user_id