CSort and self::STAT to sort by Count

Yii 1.1 General Discussion
1

I have been searching the forums and just can’t find a solution to this.

I am trying to sort a Gallery by the number of images in it. I have a Gallery table and Images table.




// Gallery model

public function relations()

{

  return array(

  'user' => array(self::BELONGS_TO, 'User', 'owner_id'),

  'images' => array(self::HAS_MANY, 'GallImg', 'gid'),

  'imgCount' => array(self::STAT, 'GallImg', 'gid'),

  );

}

and in the controller I do




$crit = new CDbCriteria(array("with" => "imgCount"));

$pages = new CPagination(Gallery::model()->count($crit));

$pages->route = "gall/index";

$pages->pageSize = SAGD::STANDARD_LIST_SIZE;

$pages->setCurrentPage($page-1);

$pages->applyLimit($crit);

$sort = new CSort('Gallery');

$sort->multiSort = false;

$sort->attributes = array('imgCount' => 'imgCount',);

$sort->defaultOrder = 'imgCount ASC';

$sort->applyOrder($crit);

$galls = Gallery::model()->findAll($crit);

which fails with error …


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

I have a simmilar problem. I want to sort a CList by statistical queries. (How) did you solve this problem?

3

I solved it like that:





		$sort = new CSort('Thing');




		$sort->attributes =array(

			'points' => array(

				'asc'=>'numberOfPoints ASC',

				'desc'=>'numberOfPoints DESC',

			)

		);

		

		$dataProvider = new CActiveDataProvider('Thing', array(


    		'criteria'=>array(

				'select' => 't.*, COUNT(point.id) AS numberOfPoints',

				'join' => 'LEFT JOIN points_table point ON point.thing_id = t.id',

				'condition' => 'published_at IS NOT NULL',

				'group' => 't.id',

			),

			'sort'=>$sort,

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

		));

and in the view:




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

	'dataProvider'=>$dataProvider,

	'itemView'=>'_listEntry',

	'sortableAttributes'=>array(

		'points',

	),

));
4

Thanks a lot. Its very helpful for me as I am implementing this first time. :)

5

The Sorting works for me. But the filter does not work. Any solution please…