Yii2在gridview中自定义sql查询

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (199)

我对Yii2很陌生。

我需要在视图中显示自定义的sql结果而不使用模型,因为我想显示一个sql视图。

index.php

<?= GridView::widget([
       'dataProvider' => $dataProvider,
       'columns' => [
            ['class' => 'yii\grid\SerialColumn'],
            'COD_RISORSA',
            [
                'label' =>"Nome",
                'attribute' => 'NOME',
                'value'=>function($data){
                    return $data["NOME"];
                }
            ],
            'COGNOME',
            ['class' => 'yii\grid\ActionColumn'],
        ],
   ]); ?>

VRisorseController.php

public function actionIndex()
{

    $totalCount = Yii::$app->db->createCommand('SELECT COUNT(*) FROM v_risorse')->queryScalar();

    $dataProvider = new SqlDataProvider([
        'sql' => 'SELECT * FROM v_risorse',
        'totalCount' => $totalCount,
        'sort' =>false,
        'pagination' => [
            'pageSize' => 10,
        ],
    ]);

    return $this->render('index', [
        'dataProvider' => $dataProvider,
    ]);
}

我有错误:

Not Supported – yii\base\NotSupportedException Message format 'number' is only supported for integer values. You have to install PHP intl extension to use this feature. 1. in C:\xampp\htdocs\advanced\vendor\yiisoft\yii2\i18n\MessageFormatter.php

提问于
用户回答回答于

controller:

$count      =   Yii::$app->db->createCommand('
                    SELECT COUNT(*) FROM screen_ticket_booking_history WHERE status=:status
                    ', [':status' => 0])->queryScalar();

                    $sql =  "SELECT A1.booking_id As Booking_id,
                                A1.booking_date As Booking_date,
                                A2.movie_name As Movie,
                                A3.theatre_name As Theatre,
                                A1.amount As Amount

                                FROM 
                                screen_ticket_booking_history A1

                                LEFT OUTER JOIN movies A2 ON A1.movie_id=A2.id
                                LEFT OUTER JOIN theatres A3 ON A1.theatre_id=A3.id
                                LEFT OUTER JOIN users_backend A4 ON A3.users_backend_id=A4.id

                                WHERE A1.booking_date = '{$day}'
                                AND   A1.movie_id='{$movies->id}'";


                    //~ $models = $dataProvider->getModels(); //print_r($models);die();
                    if( $userid != '1')
                    { 
                        $sql .= " AND A3.users_backend_id = '{$userid}' ";
                    }

                    $dataProvider = new SqlDataProvider([
                    'sql' => $sql,
                    'totalCount' => $count,
                    ]);
                    return $this->render('index',
                    [   'model'             => $model,
                        'dataProvider'      => $dataProvider,
                    ]);
                }

view:

<?= GridView::widget([
       'dataProvider' => $dataProvider,
       'columns' => [
           ['class' => 'yii\grid\SerialColumn'],

           'Booking_id',
           'Booking_date',
           'Movie',
           'Theatre',
           'Amount',
           //~ ['class' => 'yii\grid\ActionColumn'],
       ],
   ]); ?>
用户回答回答于

按下面的方式分配DB:

public function actionIndex()
{

    $totalCount = Yii::$app->db->createCommand('SELECT COUNT(*) FROM v_risorse')->queryScalar();

    $dataProvider = new SqlDataProvider([
        **'db' => Yii::$app->db,**
        'sql' => 'SELECT * FROM v_risorse',
        'totalCount' => $totalCount,
        'sort' =>false,
        'pagination' => [
            'pageSize' => 10,
        ],
    ]);

    return $this->render('index', [
        'dataProvider' => $dataProvider,
    ]);
}

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励