Yii:获取每月热门用户和年末热门用户的SQL查询
我是一个 yiibie，我有一个问题，我有一个名为 user_join_event 的表，它有 id(PK) ngo_id(FK) event_id(FK) date_created date_modified 作为对象，我想获得每个月的 5 个用户 ID(在此表中重复次数最多).像1月前5名用户，2月前5名用户等等，然后是这张表中年底前5名的用户，就像一年结束后，它应该再次给出整个月的前5名用户.请帮我解决这个问题，如何为此编写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));并且对于这个月，您可以使用此查询从 1 到 12 进行循环，并使用循环中的值设置 $yourMonth
$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>';}}}?><div><?php//年份foreach($userYear 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>
相关文章