Homeowners post jobs. Jobs are carried out by Tradesman. Homeowners review the way a job was carried out (essentially they rate a Tradesman) by a review.
Homeowners and Tradesman are both instances of model User differentiated by a role id, Besides User I have also created a Job and a Review model.
A Review consists besides title and description, of 6 different rating entities, on a scale of 1 - 10 (flexibility, thinking, appointments, friendliness, priceperformance & quality). The average rating of a Job is the sum of those 6 entities divided by 6.
Reviews are related to Jobs via the following relation;
'job' => array(self::HAS_ONE, 'Job', 'review_id'),
Job to Reviews
'review' => array(self::BELONGS_TO, 'Review', 'review_id'),
A job is related to a Homeowner (User) via the relation:
'createUser' => array(self::BELONGS_TO, 'User', 'create_user_id'),
And a Homeowner (User) to his posted Job:
'createdJobs' => array(self::HAS_MANY, 'Job', 'create_user_id'),
A Job is related to his Tradesman (User) by relation:
'carriedOutByUser' => array(self::BELONGS_TO, 'User', 'carried_out_by_user_id'),
And a Tradesman (User) to the job he carried out:
'completedJobs' => array(self::HAS_MANY, 'Job', 'carried_out_by_user_id'),
Ok, what i want to do is create a list of Tradesman, and sort them on their rating.
I create the folowing dataprovider in my userController file:
/**
* Lists all models.
*/
public function actionIndex()
{
$dataProvider=new CActiveDataProvider(User::model()->active()->isTradesman());
$this->render('index',array(
'dataProvider'=>$dataProvider,
));
}
"active" and "isTradesman" are scopes which filter active Users who have the Tradesman role.
In the Review model I created a method:
public function getScore(){
return ($this->flexibility + $this->thinking + $this->appointments + $this->friendliness + $this->priceperformance + $this->quality)/6;
}
So, per Job I can get the average score. I also tried to get this score via a relation like this:
'score' => array(self::BELONGS_TO,'Review','review_id','select' => 'SUM(score.flexibility+score.quality+score.appointments+score.priceperformance+score.thinking+score.friendliness)/6 as total')
I also tried it via a STAT relation, but i cant get it to work, so i choose the getScore() method approach.
Now i have to count all these scores on a Tradesman basis, and divide them to the number of Jobs he/she carried out. Then I want to sort the dataProvider in the userController::actionIndex() by this value.
Can you please guide me into the right direction?