Sorting dataprovider on Statistical query relation

I have a statistical query in a model’s relations called ‘rating’ that returns a SUM calculation of a field in a related model. Now I want to sort the items in my DataProvider based on this statistical query. I can call $data->rating inside the item view, but using ‘rating’ in ‘defaultOrder’ results in the following error:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'rating' in 'order clause'

Relevant lines in the ‘Idea’ model relations function:

            'votes' => array(self::HAS_MANY, 'Vote', 'idea_id'),

            'rating' => array(self::STAT, 'Vote', 'idea_id', 'select'=>'SUM(vote)'),

The DataProvider in question:

        $DataProvider=new CActiveDataProvider('Idea',array(

            'criteria'=>array(

                'condition'=>'challenge_id=:challenge',

                'params'=>array(':challenge'=>$model->id),

            ),

            'sort'=>array(

                    'defaultOrder'=>'createTime DESC',

                ),

            'pagination'=>array(

                'pageSize'=>30,

            ),

        ));

How can I change ‘defaultOrder’ to something like ‘rating ASC, createTime DESC’?

I need to order the items based on the rating field. Tried adding ‘with’=> array(‘rating’), to the criteria but that didn’t help.

Quick addition: I actually don’t need the ability to sort. I just need to display them in the right order. Using the ‘order’ criteria and removing the ‘sort’ array would thus be fine for me too.

I think you need to redeclare the relatin like that:

'rating' => array(self::STAT, 'Vote', 'idea_id', 'select'=>'SUM(vote) AS rating_value'),

and then do something like this:

        $DataProvider=new CActiveDataProvider('Idea',array(

            'criteria'=>array(

                'condition'=>'challenge_id=:challenge',

                'params'=>array(':challenge'=>$model->id),

                'with'=>array('rating'),

            ),

            'sort'=>array(

                    'defaultOrder'=>'rating.rating_value DESC',

                ),

            'pagination'=>array(

                'pageSize'=>30,

            ),

        ));

Tried it. That results in the same error.

I think I might need some kind of JOIN, because I guess anything that is a relation would not be a column of this model (read: table) by default. Haven’t figured out how it should work though…

did you add the ‘with’ to the criteria??

Test this:

'rating' => array(self::STAT, 'Vote', 'idea_id', 'select'=>'SUM(vote)','alias'=>'rating'),

and then:

        $DataProvider=new CActiveDataProvider('Idea',array(

            'criteria'=>array(

                'condition'=>'challenge_id=:challenge',

                'params'=>array(':challenge'=>$model->id),

                'with'=>array('rating'),

            ),

            'sort'=>array(

                    'defaultOrder'=>'rating.rating_value DESC',

                ),

            'pagination'=>array(

                'pageSize'=>30,

            ),

        ));

Doesn’t work. CStat doesn’t have an ‘alias’ property so now I get an error about that.

Adding ‘with’ just eager-loads it, but it’s still a seperate model. I need it to be part of the Idea model in order to be able to apply an ‘order’ criterion on it.

Still think this means I need to think of some kind of JOIN, but haven’t been able to write it correctly yet. including the Vote model and using a ‘select’ criterion in which I do the same ‘SUM(Vote.vote) AS rating’ calculation would be an option but I’m strugling with writing the JOIN.

Hello,

I was looking for a solution to this problem too and find these two links:

• http://www.yiiframework.com/forum/index.php?/topic/3944-sorting-with-cstatrelation/page__hl__statistical__fromsearch__1

• http://code.google.com/p/yii/issues/detail?id=536

so if it is a won’t fix what is the best way to achieve this? writing sql to join the tables by hand?

thanks.

bye,

Giovanni.

If this is something they won’t fix then I guess there are two options:

[list=1][]Write raw queries in stead of using AR.[]Extend your database tables with some fields to store the information that you currently obtain through the statistical queries.[/list]

Think I’ll go with 1 for now. Extending the database means I have to check all the code for places I have to add updateCounters()

Hello,

please see also this post: http://www.yiiframework.com/forum/index.php?/topic/9494-ar-statistical-query-or-manual-sql-query

I’ve explained that with an example and posted my solution and so did another user (Mike)

bye,

Giovanni.

I wanted to sort on a STAT relation and found out Yii couldn’t do it. I worked around this by creating a SQL view with the information in it and making an AR for the view. Make sure to set the primaryKey() of the AR.

I believe that Yii does not guarenttee that AR’s work with views, but it works for me right now.

Greg

Hello

also wanted to sort by a calculated column in a related model

2 models

User

related to (HAS_MANY)

UserHistory (fk : user_id)

UserHistory has a ‘points’ column

I need to sort a CgridView by the SUM of a User’s points

so I succeeded by :

declaring a relation for UserHistory in User model :

'history' => array(self::HAS_MANY, 'UserHistory', 'user_id'),

in the ‘search’ method of User model :: augmenting the CdbCriteria instance with :

$criteria->with = array('history' => array('select' => 'SUM(history.points) as user_points'));

$criteria->together = true;

$criteria->group = "t.id";

adding a custom CSort instance to CActiveDataProvider

$sort = array('attributes'=>array(

		    'points'=>array(

		        'asc'=>'history.points',

		        'desc'=>'history.points DESC',

		        'label'=>'Points',

		        'default'=>'desc',

		    ),

		    '*',

		));

		

return new CActiveDataProvider(get_class($this), array(

	'criteria'=>$criteria,

	'sort' => $sort

));

adding a column to my CGridView

$this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'user-grid',

	'dataProvider'=>$model->search(),

	'filter'=>$model,

	'columns'=>array(

		'firstname',

		'lastname',

		'email',

		'type',

		array(

			'name' => 'points',

			'value'=>'$data->points',

		),

		array(

			'class'=>'CButtonColumn',

		),

	),

));

and this does the trick

I might be overlooking something so please let me know if you find something to say about this

will there be any issues with paging ?

I’ll report anything I can find

hope this helps