寒碜的编码及改进

背景：

有一个讲座表（speech）、讲座分类表（theme_type）、教工表（teacher）、学院表（college）
需要计算出讲座表中 某个学院、所有分类、某年、所有月份的讲座数量。

我寒碜的写法：

public function detail($pk) {
        $pk=$pk?:I('pk');
        $y = I('year')?:date('Y',TIME);
        $page = I('page');
        switch (I('page')){
            case 'last':
                $y=--$y;
                break;
            case 'next':
                $y=++$y;
                break;
            default :
                $y = date('Y',TIME);
        }
        $coll_info = $this->where(array($this->getPk()=>$pk))->field('in_charge_id')->find(['hy'=>true]);
        $associate = array(
            'teacher|in_charge_id|user_id|department_id',
            'college|teacher.department_id|id|id AS coll_id,name AS college_name',
            'theme_type|theme_id|id|theme_name'
        );
        $theme_sum = M('theme_type')->where(array('status'=>array('lt',9)))->count();
        $th = M('theme_type')->where(array('status'=>array('lt',9)))->select();
        $theme = [];
        foreach($th as $v){
            $theme[$v['id']]=$v['theme_name'];
        }
        $sum = [];

// 看这里
        for($c = 1; $c <= $theme_sum; $c++){
          for($m = 1; $m<=12; $m++) {
            if($m < 10) {
              $m='0'.$m;
              $sum[$theme[$c]][]=$this->associate($associate)
                ->where(array('college.id'=>$coll_info['department_id'],'speech_time'=>array('LIKE',$y.'-'.$m.'%'),'theme_type.id'=>$c,'check_status'=>2,'status'=>array('lt',9)))
                ->count();
            }else {
              $m .='';
              $sum[$theme[$c]][]=$this->associate($associate)
                ->where(array('college.id'=>$coll_info['department_id'],'speech_time'=>array('LIKE',$y.'-'.$m.'%'),'theme_type.id'=>$c,'check_status'=>2,'status'=>array('lt',9)))
                ->count();
            }
          }
        }
// 这里为止

        $y >= date('Y',TIME) ? $next = false : $next = true;
        return array(
            'str' => array('year'=>$y,'pk'=>$pk,'noNext'=>$next),
            'json' => json_encode(array('theme'=>$theme,'sum'=>$sum),JSON_UNESCAPED_UNICODE)
        );
    }

想要这样的结果：

问题：

1、这样效率太低了！数据量一大就die掉了！数据操作不要写在循环里！
2、if else也好寒碜！可以用三元运算符嘛！高级点可以用sprintf('%2d',$m)
3、分类的id:$c怎么从1开始？如果数据库中某一个分类删除了咋办！

改进：

public function detail($pk) {
        $pk=$pk?:I('pk');
        $y = I('year')?:date('Y',TIME);
        switch (I('page')){
            case 'last':
                $y=--$y;
                break;
            case 'next':
                $y=++$y;
                break;
            default :
                $y = date('Y',TIME);
        }
        $coll_info = $this->where(array($this->getPk()=>$pk))->field('in_charge_id')->find(['hy'=>true]);
// 改进-自己写sql
        $result = $this->query("SELECT SUBSTRING(speech.speech_time, 6, 2) AS `speech_month`,speech_time, speech.theme_id, theme.theme_name,COUNT(speech.id) AS `speech_count` FROM `zsjy_speech` AS `speech` INNER JOIN `zsjy_teacher` AS `teacher` ON speech.in_charge_id = teacher.user_id  INNER JOIN `zsjy_theme_type` AS `theme` ON speech.theme_id = theme.id  INNER JOIN `zsjy_college` AS `college` ON teacher.department_id = college.id WHERE college.id = ". $coll_info['department_id'] ." AND speech.speech_time LIKE '". $y ."%'  GROUP BY speech_month, theme.id");
// 至此 整理出所需数据
        $th = M('theme_type')->where(array('status'=>array('lt',9)))->select();
        $theme = array();
        $sum = array();
        foreach($th as $v){
            $theme[$v['id']]=$v['theme_name'];
            for($i = 1; $i<=12; $i++) {
                $sum[$v['theme_name']][$i] = 0;
                foreach($result as $vv){
                    if($vv['theme_name'] == $v['theme_name'] && $vv['speech_month'] == $i) {
                      $sum[$v['theme_name']][$i] = $vv['speech_count'] - 0;
                    }
                }
            }
        }
        $y >= date('Y',TIME) ? $next = false : $next = true;
        return array(
            'str' => array('year'=>$y,'pk'=>$pk,'noNext'=>$next),
            'json' => json_encode(array('theme'=>$theme,'sum'=>$sum))
        );
    }

当sql比较复杂的时候就直接用 Model->query(sql)效率更高点!代码也少点，取出来再在外面整理成想要的格式！