Cdbcriteria Select Count


i have tow tables : posts and comments, i need to get the most comment posts,

here is my relations:

in posts table


in Comment table:


i need to execute this sql request :

SELECT  `s`.`id`, COUNT(*) AS cnt FROM `table_posts` as `s` JOIN `table_comments` as `c` ON  `c`.`id_post`=`s`.`id` GROUP BY `s`.`id` ORDER BY cnt desc



take a look at this wiki:


i can’t get posts ordered by count comments number!!!

Dear Friend

Here is one implementation.

1.Make a public property commentCount in Post model and make it safe on search.

public $commentCount;

array('commentCount', 'safe', 'on'=>'search'),


public function search()


		// Warning: Please modify the following code to remove attributes that

		// should not be searched.

		$criteria=new CDbCriteria;



		$criteria->select="t.*,(SELECT COUNT( FROM tbl_comment WHERE AS commentCount"; 










                $criteria->compare('(SELECT COUNT( FROM tbl_comment WHERE',$this->commentCount);//making the filters work

		return new CActiveDataProvider($this, array(








Now in admin.php add the commentCount as a column.

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










                        'commentCount' //commentCount added as a column.


If you have created any default scopes involving sorting post table, you have to remove it.



thanks a lot it works, but in this solution we don’t need to use the relation HAS_MANY between post and comments!


In controller

public function actionMyList() {

        $criteria = new CDbCriteria;

        $criteria->select = "t.*";

        $criteria->condition = "t.user_id=:user_id";

        $criteria->params = array(':user_id'=>Yii::app()->user->id);

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


        $model = FriendList::model()->findAll($criteria);

        $this->render('mylist', array('model' => $model));


in view result of table list view fr example

<table class="table table-bordered">




         <th>Firend Name</th>

         <th>Number of Visit</th>



   <?php if(!empty($model)): 



      <?php   foreach($model as $model_data):



         <td><?php echo $model_data->id; ?></td>

         <td><?php echo $model_data->friend->name; ?></td>

         <td><?php echo $model_data->count(1); ?></td>


      <?php endforeach; ?>


   <?php endif; ?>