Advanced Sorting Of Activedataprovider

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());





"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?