Yii: Getting the most repeated id's from a certain table

Question

I am a yiibie, and I am trying to run a query on the table named user_join_event by which I can get the 5 user_id which are most repeated in this table, so that I can have the 5 most active users id. The query is

SELECT `user_id`
  FROM `user_join_event`
 GROUP BY `user_id`
 ORDER BY COUNT(*) DESC
 LIMIT 5;

This query gives me the perfect result and I have transformed this query so that I can use it in yii which is:

$sql = Yii::app()
    ->db
    ->createCommand()
    ->select('user_id')
    ->from('user_join_event')
    ->group('user_id')
    ->order('COUNT(*) DESC')
    ->limit(4)
    ->queryAll()

Now after that I have made a allstats function in UserJoinEvent controller which is:

public function actionAllstats()
{
    $rbmodel = UserJoinEvent::model()->findAll();
    $this->layout = 'main';
    $sql = Yii::app()
        ->db
        ->createCommand()
        ->select('user_id')
        ->from('user_join_event')
        ->group('user_id')
        ->order('COUNT(*) DESC')
        ->limit(4)
        ->queryAll();

        $this->render('allstats', array("sql" => $sql, "rbmodel" => $rbmodel));
}

And a allstat.php (in the view file of UserJoinEvent) which is:

<div>
    <?php foreach ($rbmodel as $show): ?>
        <h3><?php echo $show->user_id?></h3>
    <?php endforeach; ?>
</div>

so that I can have the top 5 members id on this view page. But now, according to this code it is giving me all the user_id from the UserJoinEvent table instead of the 5 id's which are most repeated in the table. Please help me in getting the 5most repeated user's id. Thank you.

Answer 1

I think you should get the model only by the query and use them, (In your view you are echo the result of a full access to the UserJoinTAble) try modifing you code in this way

    public function actionAllstats()
            {

        $this->layout='main';
        $user=UserJoinEvent::model()->findAll(array(
                'select'=>'user_id',
                'group'=>'user_id',
                'order'=>'COUNT(*) DESC',
                'limit' =>  4
             ));  
        $this->render('allstats', array("user"=>$user));
            }


    And a allstat.php(in the view file of UserJoinEvent) which is

    <div>
        <?php
            foreach($user as $show)
            {
                echo '<h3>' . $show->user_id . '</h3>';
            }
        ?>
    </div>