Sorting ActiveDataprovider by virtual attribute

Question

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 this be done and if so, how?

Answer 1

It seems that this can't be done using php only. So i altered my query via the search method in the User model like this;

$criteria = new CDbCriteria;
$criteria->alias = 'usr';

$criteria->join = 'LEFT JOIN bp_job AS job ON `job`.`carried_out_by_user_id` = `usr`.`id` LEFT JOIN bp_review AS r ON r.id = job.review_id ';
$criteria->select = '(SUM(r.flexibility + r.quality + r.appointments + r.priceperformance + r.thinking + r.friendliness) / 6) / count(r.id) as score';
$criteria->order = 'score DESC';