Incorrect itemcount with gridview pagination

Hi all,

I’ve been struggling building an application using the Yii framework; I’ve managed to get most of the problems sorted by now, however, I’m still stuck with a weird pagination error.

I have a page with a gridview. There are many relations in the search filters (which all function fine), however, the pagination doesn’t function correctly. In this example, I’ve put in 2 items. Item #1 has amount 13, Items #2 has amount 2. For design reasons, each amount has its own row, so when one item with amount 13 is stored, 13 rows are stored in the table. In the example above, the pagination shows the following: Displaying 1-2 of 15 result(s).

The first part seems to be ok (there are only 2 results in the current page; however, the amount of results is off. This causes the pagination to think there are more pages to display results (I’ve set the pagelimit to 10), so the gridview gives me 2 pages of ‘data’, which of course is incorrect (second page just is an empty resultset).

In my search function, I’ve identified the problem line I think:

$criteria->with = array('stores_rel','neworders_rel','items_rel','suppliers_rel');	

If I comment this line, the result set seems to be ok (Displaying 1-2 of 2 result(s).), however, my search filters won’t work anymore.

When I test the original query from the Yii debugger, my rowcount is 2 (as expected), however, the Itemcount function still returns 13 somehow.

I’ve searched the internet (with friend google) and the Yii website, but haven’t found anything with a solution in it. Anyone has a clue how to force the correct itemcount on the gridview?

NOTE: this only seems to happen with relations. Is there a secret line of code to include in gridview or the search function?

Right now the search function has the following elements:

$criteria->group (combine similar entries)

$criteria->scopes (sum the amount of items)

$criteria->with (including relations to make the search filter work)

$criteria->condition (the usual stuff)

If anyone has any ideas whether it should be considered a bug or if my code is missing some vital ‘initializer’ to make it work?

Thanks in advance.

After some digging in the yii code, I’m more and more convinced it’s a bug. The incorrect value seems to be getting pulled from:

protected function calculateTotalItemCount()

	{

		$baseCriteria=$this->model->getDbCriteria(false);

		if($baseCriteria!==null)

			$baseCriteria=clone $baseCriteria;

		$count=$this->model->count($this->getCriteria());

		$this->model->setDbCriteria($baseCriteria);

		return $count;

	}

For some reason, $count doesn’t reflect the proper value. With more debugging, it seems that $this->model->count() doesn’t produce the correct value.

With the example above (15 rows total, grouped and summed, query producing 2 rows, 1 with sum amount of 13, 1 with sum amount of 2). $this->model()->count($criteria) does display 15.

I think I finally solved it by setting the totalItemCount property in the dataprovider to

'totalItemCount'=>count($this->model()->findAll($criteria)),

So instead of using the count() function of model() (which yields an incorrect resultset), I count the results of the query (which is correct).

please submit a bug, with steps how to reproduce it

IS it mysql database or are you using other databases ? Check if there is already a bug reported for it, if not chances are there might be problem in the code. I believe this is a major bug to be overlooked and think its more related to programming or database schema related.

Couldn’t find a similar bug in the tracker, so I created Bug #2904. I’m using MySQL, but I don’t think the problem is in the code. I’ve created a fresh project to recreate the problem; it seems it only occurs when you combine the ‘group’ and ‘with’ criteria in the dataprovider. When I remove the ‘with’ portion, the totalItemCount is correct, but the filters won’t work anymore then. When I add the ‘with’ portion, the filters function fine but the result (and therefor the pagination) is incorrect.

I don’t believe MySQL to be the problem, as the example query returns the correct amount of rows.

Example data:

2 tables, table #1 linked with a BELONGS_TO relation to table #2. Say I have 4 rows with a type column (which belongs to the relation).

table #2: (2 rows)

ID, TYPEDESC

1, Type Description 1

2, Type Description 2

table #1: (4 rows)

ID, DESCRIPTION, TYPEID

1, Description 1, 1

2, Description 2, 2

3, Description 3, 1

4, Description 4, 2

When I set the pagination to 3 results per page, the gridview shows 2 rows of data (as expected), but summary text on top displays Displaying 1-2 of 4 result(s). The pagination shows me there are 2 pages of data. (as it derives this number from the count() (4). It thinks it displays 3 items on page 1, and 1 on page 2 (pressing on page 2 results in an empty set though.

So it’s weird the count() function of the model ($model->count($criteria) gives me a result of 4 with the same criteria and counting the number of objects in the result query (count($model->findAll($criteria)) gives me a result of 2.

Don’t get me wrong, I rather see the error in my coding than in Yii code, but it’s still weird that it doesn’t work as expected, right?