Yii2 Gridview显示4个相同的结果页面 [英] Yii2 Gridview Displaying 4 identical Pages of Results

问题描述

---

我正在使用基于yii2高级应用程序模板的Web应用程序。我写了一个数据库查询，并通过findbysql（）实现它，通过从gii CRUD生成的网格视图返回正确的记录。但由于某种原因，返回了4页相同的结果。每个页面按顺序显示相同的主键（1-10），而元素索引在所有4页（1-40）内线性增加。

我已注释掉所以我想我不需要包含CRUD .php文件。

  public function actionIndex（）
 {
 
 $ sql ='SELECT videos.idvideo，videos.filelocation，events.event_type ，events.event_timestamp 
 FROM（（（ispy.videos videos 
 INNER JOIN ispy.cameras cameras 
 ON（videos.cameras_idcameras = cameras.idcameras））
 INNER JOIN ispy.host_machines host_machines 
 ON（cameras.host_machines_idhost_machines = 
 host_machines.idhost_machines））
 INNER JOIN ispy.events events 
 ON（events.host _machines_idhost_machines = 
 host_machines.idhost_machines））
 INNER JOIN ispy.staff staff 
 ON（events.staff_idreceptionist = staff.idreceptionist）
 WHERE（staff.idreceptionist = 182）
 AND（events.event_type IN（23,24））
 AND（events.event_timestamp BETWEEN videos.start_time 
 AND videos.end_time）'; * / 
 $ query = Videos :: findBySql（$ sql）; 
 
 $ dataProvider = new ActiveDataProvider（[
'query'=> $ query，
]）; 
 
 return $ this-> render（'index'，[
'dataProvider'=> $ dataProvider，
]）; 
 
 
 
 
 $ h $ View $ h 
 
 
 <？= GridView :: widget（[
'dataProvider'=> $ dataProvider，
'columns'=> [
''class'=>'yii'grid'SerialColumn']，
'idvideo'，
'event_type'，
'event_timestamp'，
'filelocation' ，
 // ['class'=>'yii\grid\ActionColumn']，
]，
]）; ？> 
  
请让我知道我是否需要更具体或包含任何其他信息。
 
 
 
 
 
 
谢谢你 
 
解决方案
问题在于分页需要SQL修饰符 offset ，它对 findBySql 的结果没有影响。请参阅 Yii文档。
 
 
 
您必须在控制器中使用$ _GET ['page']和$ _GET ['per-page']，例如：
  $ page = array_key_exists（'page'，$ _GET）？ $ _GET ['page']  -  1：0; 
 $ perPage = array_key_exists（'per-page'，$ _GET）？ $ _GET ['per-page']：30; 
 $ offset = $ page * $ perPage; 
 
 $ sql =SELECT ... 
 OFFSET $ offset LIMIT $ perPage; 
 
 $ dataProvider = new ActiveDataProvider（[
'query'=> $ query，
'pagination'=> [
 pageSize'=> 30， 
]，
]）; 
  
 


I am working on a web application based off of the yii2 advanced application template. I have written a database query and implemented it with findbysql() returning the correct records via a grid-view generated from the gii CRUD. However for some reason 4 pages of identical results are returned. Each page is showing identical primary keys (1-10) in order, while the element index increases linearly across all 4 pages (1-40).

I have commented out the action column so i figured i did not need to include the CRUD .php files 

videoController.php

public function actionIndex()
{

    $sql =  'SELECT videos.idvideo, videos.filelocation, events.event_type, events.event_timestamp
                    FROM (((ispy.videos videos
                        INNER JOIN ispy.cameras cameras
                            ON (videos.cameras_idcameras = cameras.idcameras))
                        INNER JOIN ispy.host_machines host_machines
                            ON (cameras.host_machines_idhost_machines =
                                    host_machines.idhost_machines))
                        INNER JOIN ispy.events events
                            ON (events.host_machines_idhost_machines =
                                    host_machines.idhost_machines))
                        INNER JOIN ispy.staff staff
                            ON (events.staff_idreceptionist = staff.idreceptionist)
                    WHERE     (staff.idreceptionist = 182)
                            AND (events.event_type IN (23, 24))
                            AND (events.event_timestamp BETWEEN videos.start_time
                                   AND videos.end_time)'; */
        $query = Videos::findBySql($sql);

    $dataProvider = new ActiveDataProvider([
        'query' =>  $query,
    ]);

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

}


View

<?= GridView::widget([
    'dataProvider' => $dataProvider,
    'columns' => [
        ['class' => 'yii\grid\SerialColumn'],
        'idvideo',
        'event_type',
        'event_timestamp',
        'filelocation',
        //['class' => 'yii\grid\ActionColumn'],
    ],
]); ?>
Please let me know if i need to be more specific or include any additional information.



Thanks ahead
解决方案
The problem is that the pagination needs SQL modifier offset which has no effect on the result of findBySql. See the Yii doc.

You have to use $_GET['page'] and $_GET['per-page'] in your controller, e.g.:
$page = array_key_exists('page', $_GET) ? $_GET['page']-1 : 0;
$perPage = array_key_exists('per-page', $_GET) ? $_GET['per-page'] : 30;
$offset = $page * $perPage;

$sql = "SELECT ...
        OFFSET $offset LIMIT $perPage";

$dataProvider = new ActiveDataProvider([
    'query' =>  $query,
     'pagination' => [
         pageSize' => 30,
     ],
]);


                        
这篇关于Yii2 Gridview显示4个相同的结果页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！