Cgridview Sort Asc, Null At End?

I have a CGridView column displaying an integer that I would like to sort in ascending order.

My problem is that some of the values may be NULL, and they come up at the top of the sort.

I’d like NULL values to be at the end, with it ordered ASC…

The SQL would be something like:

select * from the_table

order by case when the_column is null then 1 else 0,

  the_column

does the defaultOrder in CArrayDataProvider offer this type of functionality?

If not, any suggestions on how to implement it?

Thanks.

Maybe try something like this:

new CArrayDataProvider($rawData, array(

    'id'=>'user',

    'sort'=>array(

        'defaultOrder'=>'case when the_column is null then 1 else 0, the_column',

        'user'=>array(

           'asc'=>'case when the_column is null then 1 else 0, the_column',

           'desc'=>'case when the_column is null then 0 else 1, the_column', //<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' /> I don't know about this part <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />

        )

    )

));

While CActiveDataProvider uses ‘ORDER’ clause to get the sorted data, CArrayDataProvider uses array_multisort() to sort the raw data. So I guess it’s not possible to use something like ‘case when … then … else …’ in the sorting conditions.

http://www.yiiframework.com/doc/api/1.1/CArrayDataProvider#sortData-detail

One thing I can think of is introducing a dedicated field for sorting which you don’t display in the grid.

'sort' => array(

    'defaultOrder' => 'sortColumn',

    'attributes' => array(

        'name' => array(

             'asc' => 'sortColumn',

             'desc' => 'name desc',

        ),

    ),

),

In the above, you are expected to have prepared the ‘sortColumn’ values like:

$data['sortColumn'] = ($data['name'] != '') ? $data['name'] : VERY_BIG_VALUE;

Dear Ian.T

This is what I found some thing useful.Though it is not a better solution.

For CArrayDataProvider:

If column value is null, we are going to give a massive number

that is greater than maximum value in the column.

When page loads we are going to get the display in ascending order with null values at bottom.

When we click the sorter, we are going to get default order in the table or

descending order with null values at bottom.

public function search()

	{

		


		$criteria=new CDbCriteria;

		.........................

                .........................

		$criteria->order="IFNULL(column_name,10000000) ASC";//some big value that is beyond the scope.

                .................................................

                ................................................

		$objects=AR::model()->findAll($criteria);


        return new CArrayDataProvider($objects,array(

              'sort'=>array('attributes'=>array('attributeName'=>array('desc'=>'column_name DESC')))

            ));

		

	}

For CActiveDataProvider:

Here we can not apply the order beforehand in CDbCriteria.

public function search()

	{

		$criteria=new CDbCriteria;

		.........................

                .........................

              ///  $criteria->order="IFNULL(column_name,10000000) ASC"; This makes the sorter inactive.


return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'sort'=>array('attributes'=>array('attributeName'=>array('asc'=>'IFNULL(column_name,10000000) ASC','desc'=>'column_name DESC')))

		));


}

I hope there are better solutions to this.

Regards.

I just had a similar issue:

$crit = new CDbCriteria;

$crit->with = array('user');

$provider = new CActiveDataProvider(

	__CLASS__, 

	array(

		'criteria' => $crit,

		'sort' => array(

			'attributes' => array(

				'userName' => 'user.name',

				'userEmail' => 'user.email',

				'userZipcode' => 'user.zipcode',

				'userWinner' => '-t.winnerRank DESC',

			),

			'defaultOrder' => '-t.winnerRank DESC'

		),

	)

);

The defaultOrder works perfectly. The sorting, however - does not.

The query outputs to something like this: [sql]ORDER BY -t.winnerRank DESC[/sql]

And that’s not valid SQL, so it will fail. Had I been able to just remove the ticks, so it looked like this:

[sql]ORDER BY -t.winnerRank DESC[/sql]

it would work just fine.

Still no valid solutions to this problem?