YII2原生SQL分页支持排序搜索

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;
}

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

        <?php

        foreach ($models as $res) {
            echo $res['id'].'->->->>'.$res['title'];
            echo "<br/>";
        }
        ?>
        //排序
        <?php
        echo $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(); ?>