YII2默认情况下会生成一个直接操作单表的模型并且具备搜索和分页以及排序功能，在很多复杂的业务逻辑需求中，单表操作很难实现我们想要的效果，，此时我要是选择的话就用纯sql来做，不用考虑那么多的对应关系，而且你得SQL语句执行效率越高，程序执行的效率也就越高。
OK我们来看看怎么实现。
一、首先我们来看一个需求：在一个博客的首页中，需要显示博客列表，列表中（作者，文章标题，发表时间，文章分类，评论数量）需要显示。
二、建表
yii_article(文章):[id,title,publish_time,user_id,cate_id,content,sort]
yii_cate(分类):[id,cate_name,pid,sort]
yii_user(用户):[id,uname,realname,uemail,password_hash,password_reset_token,auth_key]
yii_comment(评论):[id,user_id,comment_time,content,pid,article_id]
三、SQL语句
SELECT `yii_user`.`realname`,`yii_article`.*,`yii_cate`.`cate_name`,(select count(`yii_comment`.`id`) from `yii_comment` where `yii_comment`.`article_id`=`yii_article`.`id`) as `comment_num` FROM `yii_article` LEFT JOIN `yii_user` ON `yii_user`.`id`=`yii_article`.`user_id` LEFT JOIN `yii_cate` ON `yii_cate`.`id`=`yii_article`.`cate_id`
四、实现
模型
ArticleSearch.php
public function search($params){$sql="SELECT `yii_user`.`realname`,`yii_article`.*,`yii_cate`.`cate_name`,(select count(`yii_comment`.`id`) from `yii_comment` where `yii_comment`.`article_id`=`yii_article`.`id`) as `comment_num` FROM `yii_article` LEFT JOIN `yii_user` ON `yii_user`.`id`=`yii_article`.`user_id` LEFT JOIN `yii_cate` ON `yii_cate`.`id`=`yii_article`.`cate_id`";$get=Yii::$app->request->get();if ($id = $get['ArticleSearch']['id']) {$sql.=" WHERE `yii_article`.`id`=".$id;}//这些搜索条件你都可以一个个加上，为了节约时间我就只写id一个了 $rows=Article::findBySql($sql)->all();$dataProvider = new SqlDataProvider(['sql' => $sql,//'params' => [':status' => 1],'totalCount' => count($rows),'sort' => ['attributes' => ['id'=>['asc' => ['yii_article.id' => SORT_ASC],//yii_article.id如果你不是多表你可以直接写id'desc' => ['yii_article.id' => SORT_DESC],'default' => SORT_ASC,'label' => 'ID-NUM',],],],'pagination' => ['pageSize' => 5,],]);$this->load($params);if (!$this->validate()) {// uncomment the following line if you do not want to any records when validation fails// $query->where('0=1');return $dataProvider;}return $dataProvider;}控制器文件 ArticleController.php
public function actionIndex(){$searchModel=new ArticleSearch();$dataProvider=$searchModel->search(Yii::$app->request->queryParams);$models = $dataProvider->getModels();return $this->render('index', ['models'=> $models,'page'=>$dataProvider->pagination,'sort'=>$dataProvider->sort,'searchModel'=>$searchModel,]);}
视图文件 view/article/index.php
<?phpforeach ($models as $res) {echo $res['id'].'->->->>'.$res['title'];echo "<br/>";}?>//排序<?phpecho $sort->link('id') . ' | ';//分页echo LinkPager::widget(['pagination' => $page,]);?>//搜索表单<?php $form = ActiveForm::begin(['action' => ['index'],'method' => 'get',]); ?><?= $form->field($searchModel, 'id') ?><?= $form->field($searchModel, 'user_id') ?><?= $form->field($searchModel, 'cate_id') ?><?= $form->field($searchModel, 'publish_time') ?><div class="form-group"><?= Html::submitButton('Search', ['class' => 'btn btn-primary']) ?><?= Html::resetButton('Reset', ['class' => 'btn btn-default']) ?></div><?php ActiveForm::end(); ?>
你说只有有缘人才可以取下，我看着你手中的戒指，
