Yii:获取每月热门用户和年末热门用户的SQL查询

 公共函数 actionAllstats(){$this->layout='main';$user=UserJoinEvent::model()->findAll(array('条件' =>'YEAR(date_created)=:year 和 MONTH(date_created)=:month','参数' =>数组(':year'=>2015, ':month'=>$yourMonth),'选择'='=>'user_id','组'=>'user_id','订单'=>'COUNT(*) DESC','限制' =>5));$this->render('allstats', array("user"=>$user));}

<?phpforeach($user 作为 $show){for($i = 1 ; $i <=12 ; $i++){if($yourMonth == $i){$model = User::model()->findByAttributes(array('id'=>$show->user_id,));如果(isset($模型)){回声'<h3>'.$show->user_id .' - ' .$model->用户名.'</h3>';}别的 {回声'<h3>'.$show->user_id .' - 未找到用户名</h3>';}}}}?>

 $user=UserJoinEvent::model()->findAll(array('条件' =>'YEAR(date_create)=:year','参数' =>数组(':year'=>2015)'选择'='=>'user_id','组'=>'user_id','订单'=>'COUNT(*) DESC','限制' =>5));

 $user=UserJoinEvent::model()->findAll(array('条件' =>'YEAR(date_create)=:year 和 MONTH(date_create)=:month','参数' =>数组(':year'=>2015, ':month'=>$yourMonth)'选择'='=>'user_id','组'=>'user_id','订单'=>'COUNT(*) DESC','限制' =>5));

公共函数 actionAllstats(){$this->layout='main';$myCurrYear = 日期(Y")$userYear=UserJoinEvent::model()->findAll(array('条件' =>'YEAR(date_create)=:year','参数' =>数组(':year'=>$myCurrYear)'选择'='=>'user_id','组'=>'user_id','订单'=>'COUNT(*) DESC','限制' =>5));$this->render('allstats', array("userYear"=>$userYear));}

<?php//这十二个月for($month = 1 ; $month <=12 ; $month++) {<div>$user=UserJoinEvent::model()->findAll(array('条件' =>'YEAR(date_created)=:year 和 MONTH(date_created)=:month','参数' =>数组(':year'=>2015, ':month'=>$month),'选择'='=>'user_id','组'=>'user_id','订单'=>'COUNT(*) DESC','限制' =>5));foreach($user as $show) {$model = User::model()->findByAttributes(array('id'=>$show->user_id,));如果(isset($模型)){回声'<h3>'.$show->user_id .' - ' .$model->用户名.'</h3>';}别的 {回声'<h3>'.$show->user_id .' - 未找到用户名</h3>';}}

I am a yiibie, I have a question that I have a table named user_join_event which has id(PK) ngo_id(FK) event_id(FK) date_created date_modified as objects, and I want to get the 5 users Id's(which are repeated the most in this table) for every month. Like January top 5 users, Feb top 5 users and so on and then the top 5 users at the end of the year from this table, like once the year is completed it should give again the top 5 users from all over the month. Please help me with this, how to write an SQL query for this, thank you.

 public function actionAllstats()
            {

        $this->layout='main';
        $user=UserJoinEvent::model()->findAll(array(
            'condition' => 'YEAR(date_created)=:year and MONTH(date_created)=:month',
            'param' => array(':year'=>2015, ':month'=>$yourMonth),
            'select'=>'user_id',
            'group'=>'user_id',
            'order'=>'COUNT(*) DESC',
            'limit' =>  5
         ));  
        $this->render('allstats', array("user"=>$user));
            }

and this is my view file

<div>
    <?php

        foreach($user as $show)
        {
            for($i = 1 ; $i <=12 ; $i++)
            {
                if($yourMonth == $i)
                {
                    $model = User::model()->findByAttributes(array('id'=>$show->user_id,));
           if (isset($model)) 
               {
               echo '<h3>' . $show->user_id .  ' - ' . $model->username . '</h3>';
           } 
           else {
               echo '<h3>' . $show->user_id .  ' - Username not found</h3>';
           } 
                }
            }

        }

        ?>
    </div>

For the Year you can sue this query for getting the users

       $user=UserJoinEvent::model()->findAll(array(
            'condition' => 'YEAR(date_create)=:year',
            'params' => array(':year'=>2015)
            'select'=>'user_id',
            'group'=>'user_id',
            'order'=>'COUNT(*) DESC',
            'limit' =>  5
         ));  

and for the month you can do a loop from 1 to 12 using this query and set $yourMonth withe the value in loop

       $user=UserJoinEvent::model()->findAll(array(
            'condition' => 'YEAR(date_create)=:year and MONTH(date_create)=:month',
            'params' => array(':year'=>2015, ':month'=>$yourMonth )
            'select'=>'user_id',
            'group'=>'user_id',
            'order'=>'COUNT(*) DESC',
            'limit' =>  5
         ));  

Controller action

public function actionAllstats()
{

    $this->layout='main';
    $myCurrYear = date("Y")
    $userYear=UserJoinEvent::model()->findAll(array(
        'condition' => 'YEAR(date_create)=:year',
        'params' => array(':year'=>$myCurrYear)
        'select'=>'user_id',
        'group'=>'user_id',
        'order'=>'COUNT(*) DESC',
        'limit' =>  5
     ));  
    $this->render('allstats', array("userYear"=>$userYear));
}

View

This view in not based on good pratice, because use access to model inside a view. is a trial for lead you to understand some of the problem related with question.

<?php    // this for the twelve month

    for($month = 1 ; $month <=12 ; $month++) {
    <div>
        $user=UserJoinEvent::model()->findAll(array(
            'condition' => 'YEAR(date_created)=:year and MONTH(date_created)=:month',
            'params' => array(':year'=>2015, ':month'=>$month),
            'select'=>'user_id',
            'group'=>'user_id',
            'order'=>'COUNT(*) DESC',
            'limit' =>  5
         ));    
        foreach($user as $show) {
            $model = User::model()->findByAttributes(array('id'=>$show->user_id,));
            if (isset($model)) {
                echo '<h3>' . $show->user_id .  ' - ' . $model->username . '</h3>';
            } 
             else {
                echo '<h3>' . $show->user_id .  ' - Username not found</h3>';
            } 
        }
    </div>            
    }
?>


<div>   
    <?php // the for the year 
        foreach($userYear as $show) {
            $model = User::model()->findByAttributes(array('id'=>$show->user_id,));
            if (isset($model)) {
                echo '<h3>' . $show->user_id .  ' - ' . $model->username . '</h3>';
            } 
             else {
                echo '<h3>' . $show->user_id .  ' - Username not found</h3>';
            } 
        }
    ?>
</div>

这篇关于Yii:获取每月热门用户和年末热门用户的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！