CDbCriteria ORDER BY COUNT()?

I am trying to create a query that will sort users by their post count descending and had no luck. Can someone point me in the right direction?


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

          'criteria'=>array(

	          'join'=>'LEFT JOIN post ON post.userId = user.id',

	          'limit'=>5,

                  'order'=>'count(<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />) DESC',

          ),

  		  'pagination'=>false,

	  ));

Your criteria is missing:


  'group'=>'user.id',




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

          'criteria'=>array(

                  'join'=>'LEFT JOIN post ON post.userId = user.id',

                  'limit'=>5,

                  'order'=>'count(post.id) DESC',

                  'group'=>'user.id',

          ),

                  'pagination'=>false,

          ));






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

          'criteria'=>array(

                  'join'=>'LEFT JOIN `post` ON \'post.userId\' = \'user.id\'',

                  'limit'=>5,

                  'order'=>'count(post.id) DESC',

                  'group'=>'user.id',

          ),

                  'pagination'=>false,

          ));



For some reason it returns me only one user, the user with id 1.

Ok, I solved that issue by using the default alias t in the queries… New issue though, the query seems to work in terms of sorting… but now every record’s primary key is invalid… it looks as though it set it to the primary key of the last post it indexed.