Cdbcriteria Select Count

Hello,

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

here is my relations:

in posts table

'comments'=>array(self::HAS_MANY,'Comment','id_post'),

in Comment table:

'post'=>array(self::BELONGS_TO,'Posts','id_post'),

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

thanks.

Hi,

take a look at this wiki:

http://www.yiiframework.com/wiki/319/searching-and-sorting-by-count-of-related-items-in-cgridview

hi,

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'),

Post::search

public function search()

	{

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

		// should not be searched.


		$criteria=new CDbCriteria;


		$criteria->with="comments";

		$criteria->together=true;

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

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


		$criteria->compare('id',$this->id);

		$criteria->compare('title',$this->title,true);

		$criteria->compare('content',$this->content,true);

		$criteria->compare('tags',$this->tags,true);

		$criteria->compare('status',$this->status);

		$criteria->compare('create_time',$this->create_time);

		$criteria->compare('update_time',$this->update_time);

		$criteria->compare('author_id',$this->author_id);

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


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'pagination'=>array(

		            'pageSize'=>4),

		        'sort'=>array('attributes'=>array('commentCount'=>array(),'*')),

		

		));

	}

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

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

	'id'=>'post-grid',

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

	'filter'=>$model,

	'columns'=>array(

                  'id',

                   'title',

                    'content',

                      'create_time',

                       'update_time',

                        'commentCount' //commentCount added as a column.


..............................................................

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

Regards.

hello,

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

Example:

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">

   <thead>

      <tr>

         <th>ID</th>

         <th>Firend Name</th>

         <th>Number of Visit</th>

      </tr>

   </thead>

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

      ?>

   <tbody>

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

         ?>

      <tr>

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

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

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

      </tr>

      <?php endforeach; ?>

   </tbody>

   <?php endif; ?>

</table>