Yii Framework Forum: Cgridview Sort Asc, Null At End? - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Cgridview Sort Asc, Null At End? Rate Topic: -----

#1 User is offline   Ian T. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 24
  • Joined: 23-March 12
  • Location:Stamford, CT

Posted 12 October 2012 - 04:13 PM

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.
0

#2 User is offline   compact_corpse 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 62
  • Joined: 24-May 11

Posted 12 October 2012 - 10:32 PM

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', //??? I don't know about this part ???
        )
    )
));

0

#3 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,041
  • Joined: 16-February 11
  • Location:Japan

Posted 13 October 2012 - 12:44 AM

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.yiiframew...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;

0

#4 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 13 October 2012 - 12:24 PM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users