Pagination is not working in Yii 2 for complex SQL

Pagination is not working in Yii 2 for complex SQL. It is only working if I don’t use WHERE clause or any other complex clause in my SQL query.

I would like to get help from you guys to find out the solution for this.

Thanks.

Post your code that doesn’t work.

Thanks.

Here, I have attached my code bellow-

$find_query = "SELECT * FROM business WHERE status='Enabled' ";  	

$query = Business::findBySql($find_query); 	

//$query = Business::find()->where(['status' => 'Enabled']); 	

$countQuery = clone $query;  	

$pages = new Pagination(['totalCount' => $countQuery->count(), 'defaultPageSize' => 10]);	

$data_rows = $query->offset($pages->offset) ->limit($pages->limit) ->all();

Thanks.

$countQuery->count() returns correct value?

using pageSize instead defaultPageSize ?

Hi,

Thanks.

It’s giving me same number for both cases. But that number is not correct. If I use following query

$query = Business::find()->where(['status' => 'Enabled']); 

then its giving me correct number for both cases.

Any idea about what’s happening here?

Thanks.

1. you need to set page you want to see, otherwise it will return same items.

2. don’t clone object it doesn’t make any sense.

3. bad idea to use sql when you can use better way.

4. better use dataprovider, it will make things easier

5. your query is not complex

anyway, it should work

my dummy sample

	public function actionIndex()

	{

		$q = Books::find()->where(['ext' => 'pdf']);

		$p = new \yii\data\Pagination([

			'totalCount' => $q->count(),

			'defaultPageSize' => 10

		]);

		$p->setPage(2);

		$data = $q->offset($p->getOffset())->limit($p->getLimit())->all();

		var_dump($data);

	}