Pagination

Yii 2.0 General Discussions
1

I’m having a strange problem with gridView and pagination. This is my search function


public function search($params = [])

    {

	   $query = StandardTaskTemplate::find()

	   		    ->select('*')

	            ->join('LEFT JOIN', TaskTemplateTask::tableName(), TaskTemplateTask::tableName().'.standard_task_id = t_template_tasks_old_system.taskid')

				->join('LEFT JOIN',TaskTemplate::tableName(), TaskTemplate::tableName().'.id = t_task_template_task.task_template_id')

				->join('LEFT JOIN', SourcesTask::tableName(), SourcesTask::tableName().'.task_id = t_template_tasks_old_system.taskid')

				//->where(['task_template_id' => $params['task_template_id']])

				->orderBy('tasktext')

				

				

	

	   $provider = new ActiveDataProvider([

          'query' => $query,

		  

		 

		  		]);

	   $this->load($params);

	   $query->andFilterWhere(['=', 'task_template_id', $this->task_template_id]);

	   $query->andFilterWhere(['like', 'tasktext', $this->tasktext]);

	   $query->andFilterWhere([ '=','categoryid', $this->categoryid]);

	   $query->andFilterWhere(['=', 'state', $this->state]);

	   $query->andFilterWhere(['=', 'repeating_type', $this->repeating_type]);

	  

      return $provider;	

    }

this is my contoller




   $searchModel = new StandardTaskTemplateSearch();

   $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

   $dataProvider->setPagination(['pageSize' => '20'])

   return $this->render('templates', ['dataProvider' => $dataProvider,

				      'searchModel' => $searchModel]);

	 

    }

but on the view, the first page shows

Showing 1-14 of 1,224 items.

and the second page shows

Showing 21-40 of 1,224 items.

and I can’t see rows 15 to 20 at all? I can’t figure out why this is happening? any ideas

2

Hi helenpostle,

First of all, check the SQLs generated by the data provider when it retrieves the data for your gridview. You can easily see them using the debug toolbar.

How many SQLs do you see? I bet they are much more than you expected. It’s because you are loading the related data “lazily”.

But it’s not the main point here.

You’ll notice that the main SQL has “OFFSET” and “LIMIT” clauses that have been added by the pagination object. The main SQL should return 20 rows. But they may or may not represent 20 objects, because, I believe, you are joining “HAS_MANY” related table(s). That’s why you are getting less than 20 sometimes.

Please check the following wiki articles for more information, and you’ll know what you can do with your problem.

Wiki : Relational Query - Lazy Loadnig and Eager Loading in Yii 2.0

(https://www.yiiframework.com/wiki/834/relational-query-lazy-loadnig-and-eager-loading-in-yii-2-0)

Wiki : Drills : Search by a HAS_MANY relation in Yii 2.0

(https://www.yiiframework.com/wiki/780/drills-search-by-a-hasmany-relation-in-yii-2-0)