问题描述
我正在使用基于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
的结果没有影响。请参阅。
您必须在控制器中使用$ _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个相同的结果页面的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!