Yii2在gridview中自定义sql查询 [英] Yii2 custom sql query in gridview

问题描述

我对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（）; 
 $ b $ dataProvider = new SqlDataProvider（[
'sql'=>'SELECT * FROM v_risorse'，
'totalCount'=> $ totalCount，
' sort'=> false，
'pagination'=> [
'pageSize'=> 10，
]，
]）; 
 
 return $ this-> render（'index'，[
'dataProvider'=> $ dataProvider，
]）; 
 
 $ / code> 

在以下网址：
http：//localhost/advanced/frontend/web/index.php？r = vrisorse％2Findex

我有错误：

不支持 - yii \base \NotSupportedException消息格式'数字'
仅支持整数值。您必须安装PHP intl
扩展才能使用此功能。
1.在C：\xampp\htdocs\advanced\vendor\yiisoft\yii2\i18n\MessageFormatter.php

我试图评论gridview中的所有列，并且该错误似乎与 $ dataProvider 变量

有关

'COD_RISORSA'，'NOME'，'COGNOME'是select的列。

解决方案

您需要安装PHP intl extension.i，它具有相同的错误

是我的工作代码
in 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.movi​​e_name As Movie，
 A3.theatre_name As Theatre，
 A1.amount As Amount 
 
 FROM 
 screen_ticket_booking_history A1 
 
 LEFT OUTER JOIN电影A2 ON A1.movi​​e_id = A2.id 
 LEFT OUTER加入剧院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.movi​​e_id ='{$ movies-> id}'; 
 
 
 //〜$ models = $ dataProvider-> getModels（）; //的print_r（$机型）;模具（）; 
 if（$ userid！='1'）
 {
 $ sql。=AND A3.users_backend_id ='{$ userid}'; 
 
 $ b $ dataProvider = new SqlDataProvider（[
'sql'=> $ sql，
'totalCount'=> $ count，
 ]）; 
 return $ this-> render（'index'，
 ['model'=> $ model，
'dataProvider'=> $ dataProvider，
]）; 
} 
  

以下是我的看法

 <？= GridView :: widget（[
'dataProvider'=> $ dataProvider，
'columns'=> [
''class'=>'yii'grid \\'SerialColumn']，
 
'Booking_id'，
'Booking_date'，
'Movie'，
'剧院'，
'金额'，
 //〜['class'=>'yii\grid\ActionColumn']，
]，
]） ; ？> 
  

I'm quite new to Yii2. I'm using advanced structure

I need to show a custom sql result in a view without using a model because I would like to display a sql view.

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,
    ]);
}

At the following Url: http://localhost/advanced/frontend/web/index.php?r=vrisorse%2Findex

I have the error:

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

I tried to comment all the columns in gridview, and the error seems to be related to $dataProvider variable

'COD_RISORSA','NOME', 'COGNOME' are columns of the select.

解决方案

You need to install PHP intl extension.i had the same error

given below is my working code in 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,
                    ]);
                }

and below is my view

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

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

这篇关于Yii2在gridview中自定义sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！