CGridView + pagination + sorting problem

Archives Bug Discussions
1

How to reproduce:[list=1]

[*]Suppose we have a database table such as:




CREATE TABLE [dbo].[tbl_device](

	[id] [smallint] IDENTITY(1,1) NOT NULL,

	[name] [varchar](50) NOT NULL,

	[active] [bit] NULL

);

[*]Table contains ~300 entries, where column [active] can have only 1 or 0 ([bit] data type).

[*]Using the Gii Code Generator, we generate a model class.

[*]Now, using this model and CGridView widget with sorting capability, print out the contents of the table on the page.

[*]Sort by ‘Active’ column.

[*]Then list 5-10 pages using the ‘Next>’ button of the ‘pager’.

[/list]

Problem:

On some point we will see, that although page number is changed, the data in the grid is the same as it was on the previous page.

Moreover, this set of data (10 items by default) appears on several pages.

Configuration:

  • Yii 1.1.11

  • MS SQL Server 2012 Express

  • IIS 7

  • Windows 7

Will appreciate any help and suggestions.

2

could be duplicate entries use distinct

3

What did you mean?

If we are talking about full entry (SELECT * FROM…),

there are unique 'id’s, thus there can not be duplicate entries.

And if we are talking about one column ‘active’ (SELECT active FROM…),

of course there are duplicate values. There are only 1s and 0s for 300 entries.

Probably, I misunderstood something?

4

Hello.

If the 1.11-experienced members don’t know more about this, why don’t you post an SQL export of your table so that we can test your case?

Cheers :)

5

Please post your code, maybe there is something wrong with it, are you using custom criteria in your dataprovider?

6

Hello. :)

Technically, I am using almost ‘default’ code (with a few small changes) generated by Gii. These changes should not affect CGridView workflow.

So this is the part of code from ‘admin.php’ I used to configure and display CGridView:




<?php

$dataProvider = $model->search();

$dataProvider->pagination = array('pageSize' => 25);

$dataProvider->sort = array('defaultOrder' => 'name ASC');

$this->widget('zii.widgets.grid.CGridView', array(

    'id' => 'device-grid',

    'dataProvider' => $dataProvider,

    'filter' => $model,

    'template' => "{summary}\n{pager}<br>\n{items}\n{pager}",

    'columns' => array(

        array(

            'header' => '#',

            'value' => '$this->grid->dataProvider->pagination->currentPage*$this->grid->dataProvider->pagination->pageSize + $row+1',

        ),

        'name',

        'active',

        array(

            'class' => 'CButtonColumn',

            

        ),

));

?>

Where:

  • $model - is an ActiveRecord generated with Gii.

  • First column is a workaround to get the correct index number.

I can export my table also, but as I figured out, described strange behavior appears with all tables, where CGridView trying to sort data by a column containing a lot of identical entries.

Please, try it yourself.

If the problem appears, then it is a bug. If not, then it is a bug in my head. :)

7

have you found the problem?

we’re having exactly the same problem under MS SQL SERVER 2008 RC2 and IIS7.

8

Don’t know exactly where the bug is, but this is a workaround, which works for me:

ActiveDataProvider for the CGridView should be configured to always sort data by entry ID.

I have done it like this:

In the model`s ‘search’ method, when you create an ActiveDataProvider. In the ‘sort’ option always add in the end ‘[t].id’.




    return new MyActiveDataProvider($this, array(

        'criteria' => $criteria,

        'sort' => array(

            'defaultOrder' => '[t].osakond_id ASC,[t].seadmeliik_id ASC,[t].nimi ASC,[t].id ASC',

            'attributes' => array(

                'osakond_search' => array(

                    'asc' => '[osakond].[nimi], [seadmeliik].[nimi],[t].[id]',

                    'desc' => '[osakond].[nimi] DESC, [seadmeliik].[nimi] DESC,[t].[id]',

                ),

                'seadmeliik_search' => array(

                    'asc' => '[seadmeliik].[nimi],[t].[id]',

                    'desc' => '[seadmeliik].[nimi] DESC,[t].[id]',

                ),

                'nimi' => array(

                    'asc' => '[t].[nimi],[t].[id]',

                    'desc' => '[t].[nimi] DESC, [t].[id] DESC',

                    'label' => $this->getAttributeLabel('nimi'),

                ),

                'inventarinumber' => array(

                    'asc' => '[t].[inventarinumber],[t].[id]',

                    'desc' => '[t].[inventarinumber] DESC, [t].[id] DESC',

                    'label' => $this->getAttributeLabel('inventarinumber'),

                ),

            ),

        ),

    ));

Try it out, and please write here if it works for you.

Good luck!