Sorting by calculated data in a CActiveDataProvider

Yii 1.1 General Discussion
1

I’m trying to sort AR’s by its “rating” attribute which is not a column in the underlying table but can be calculated instead. This has to happen within a CActiveDataProvider. I’ve looked into CSort to see if I can make my own custom sorting functions and couldn’t find them. How would one do this?

2

Have you tried by directly modifying CDbCriteria used inside CActiveDataProvider, to force your own sorting?

3

Yes, it did the trick, although not exactly a solution. I guess it will do for now. :)

I tried everything to make it an attribute of the AR instead of modifying cdbcriteria directly:

  • sorting by attributes generated by scopes

  • sorting by statistical relations

It just wouldn’t work… Now I have a little overhead because of redundant database queries for ratings.

Anyone got it to sort by attributes that have no table columns?

4

It seems to me that this is not easy at all …

5

Maybe I’m missing something, or you are asking for something else, but for me this can’t be more obvious than it is in below example of code:


$criteria = new CDbCriteria;

$criteria->compare('ID', $model->ID, true);

$criteria->compare('USERNAME', $model->USERNAME, true);

$criteria->compare('RATING', function_for_calculating_rating($model->USERNAME));

$criteria->select = 'ID, USERNAME, RATING';

$criteria->order = 'RATING DESC';


$dataProvider = new CActiveDataProvider($model, array

(

    	'criteria'=>$criteria,

    	'pagination'=>array('pageSize'=>20),

))

This way you can hire your own function and compare your AR with calculation done with it. Is this, you are looking for?

6

Actually, it’s not supposed to be a compare criteria (the rating shouldn’t be searched for right?) I wanted to SORT based on a calculated value. I eventually just did it by adding two extra aliased columns named ‘sum_rating’ (sum of all ratings) and ‘total_voters’ (amount of total voters), and then put this in the CSort settings array within CDataProvider to tell it how to sort on “rating” (this is called virtual something, I can’t remember the name for it).

Setting sum_rating and total_voters columns in the query criteria:




'select' => array('*','COUNT(r.id) AS sum_rating','SUM(r.rating) AS total_voters'),

'join' => 'LEFT JOIN video_user_rating r ON (t.id = r.video_id)',

Sort settings:




'sort' =>

	array

	(

		'defaultOrder' => array('approved_time'=>true),

		'attributes' => 

			array(

				'rating'=>

					array(

						'asc'=>'sum_rating, total_voters DESC',

						'desc'=>'sum_rating DESC, total_voters DESC',

					),

				'views',

				'title',

				'approved_time'

			),

	),

So, the columns are made on the fly, and sorting them becomes possible thanks to CSort. This works. However, it would have been much more efficient if I could somehow turn sum_rating and total_voters into attributes and just sort based on them. This turns out to be a tough problem. But thanks anyway for your help!

7

I totally misunderstood you, so there is nothing to thank for.

But, on the other hand, if you are really sure that what you’ve just done can be made a simpler way, by introducing some changes to core Yii classes or changing a bit it’s behaviour, then go ahead and start a new ticket in bug tracker with feature request flag or post an new thread in similar forum folder here. After discussion, if others agree with you, this solution will eventually be incorporated into future releases of Yii.

As mdomba once told me, people behind framework growth (Qiang, samdark, mdomba himself and others) are really good developers, but are unable to predict everything what others would like to see in Yii. And feature requesting on either bug tracker or forum opened for this purpose, is the best way to tell them, how you would like to change Yii.