Default Order In Csort

Yii 1.1 General Discussion
1

Hi Comunity,

I’ve a default sort problem in a cgridview, when it shows the fisrt time the sorting does in ‘desc’ not in ‘asc’ order in the atributte ‘rif’ which is the first cgridview’s column, following is the code in the model from search method:

what I’am doing wrong, thanks in advance

$sort = new CSort();

            $sort->defaultOrder=array(


                'rif'=> array(


                    'asc'=>'CONCAT(nacionalidad, rif) ASC',


                    'desc' => 'CONCAT(nacionalidad, rif) DESC',


                    'default' => 'asc',


                ));


            $sort->attributes = array(


                'nombre_cliente'=>array(


                    'asc'=>'nombre_cliente ASC',


                    'desc'=>'nombre_cliente DESC',


                    ),


                'rif'=> array(


                    'asc'=>'CONCAT(nacionalidad, rif)ASC',


                    'desc' => 'CONCAT(nacionalidad, rif) DESC',


                    'default'=>'asc',


                    ),


                 '*', // add all of the other columns as sortable


            );


	return new CActiveDataProvider($this, array(


		'criteria'=>$criteria,


                    'sort'=>$sort,


                    'pagination' => array(


                        'pageSize' => 10,


                    ),


	));
2

Are you sure?

Try putting ‘desc’ in stead of ‘asc’ to see if it behaves the same.

3

Thanks le_top,

I’d done your suggestion and it didn’t work. The cgridview is displaying something like this in ASC:

J-1

J-2

J-3

V-8

V-7

The first field ‘nacionalidad’ sort in correct order, but the seconda field doesn’t sort correctly.

When I aplied the DESC order something like this shows:

V-8

V-7

J-1

J-2

J-3

I’m going to explain something else, the value in the cgridview’s column is a concatenation of two attributes: ‘nacionalidad’ and ‘rif’. The first one was defined in the database as string, and the second one, integer.

Here is the method in the model to display the value:

[b]protected function getRif($data,$row)

    {


            $theCellValue = $data->nacionalidad . '-'. str_pad(strval( $data->rif ),9,"0",STR_PAD_LEFT);


            $theCellValue = substr($theCellValue, 0, strlen($theCellValue)-1) .'-'. substr( $theCellValue, -1);


            return $theCellValue;


    }[/b]

the column’s code in the cgridview is:

[b]array(‘name’ => ‘rif’,

                          'value' => array( $model, 'getRif' ),


                          'header' => 'R.I.F.', 


                          'htmlOptions' => array('id' => 'rif')


                        ),[/b]

So I think in the CSort some parameter is missing.

4

Hi, I use ‘yiitbdebug’ which allows me to see the SQL request in the navigator which is often helpfull to make sure that the SQL request is what you expect.

I suggest that you add the nationalidad and rif columns to the gridview to make sure that your concatnation function is not wrong.

Also, avoid creating getRif when you already have a column ‘rif’, use ‘getComputedRif’ or ‘getDisplayRif’ and add ‘displayrif’ to your gridview columns.

5

FYI: I checked that mySql does the sorting of the concatenated string correctly, and in my case it did.

6

Thanks again, I solved the order problem doing this:

First, in the cgridview, I’d taken your advice and add two columns for attributes ‘nacionalidad’ and ‘rif’ and replace the name of ‘getRif’, here is the code:

[b]‘columns’=>array(

                    array('name' =>'nacionalidad', 


                        'value' => $model->nacionalidad,


                        'visible' => false,


                        ),


                    array('name' =>'rif', 


                        'value' => $model->rif,


                        'visible' => false,


                        ),


                    array('name' => 'rifcalculado',


                          'value' => array( $model, 'calcularRif' ),


                          'header' => 'R.I.F.', 


                          'htmlOptions' => array('id' => 'rif')


                        ),[/b]

Obviously, then method ‘calcularif’ is the same as ‘getrif’, only changed the name.

Second, in the model, in the search() method I’d changed this instruccion

[b]

‘sort’=>array(

          'defaultOrder'=>' CONCAT(`nacionalidad`, CAST(`rif` AS CHAR)) ASC',[/b]

I used the CAST funcion to change the rif to string to do the concatenation correctly.

And finally, in the database I changed the attribute rif from UNSIGNED to UNSIGNED ZEROFILL.

Now, the sorting in the cgridview is correct, but I’ve another problem, I don’t know why, I loose the option in the header to sort when I clicked.

Any help is appreciated. Thanks again.

7

Adding rif and nacionalidad in the gridview was mainly to help debugging.

I haven’t checked in detail, but I think you need an extra entry in $sort->attributes with the name of the virtual field, ‘sortable’ is set to true by default in CDataColumn.

8

Yes, your right, I deleted the columns ‘nacionalidad’ and ‘rif’ from cgridview, and changed the column ‘rifcalculado’ (virtual field) by the attribute ‘rif’, here is the code:

                   [b]array('name' => 'rif',


                          'value' => array( $model, 'calcularRif' ),


                          'header' => 'R.I.F.', 


                          'htmlOptions' => array('id' => 'rif')


                        ),[/b]

and in the model’s method seach() I change the code:

[b] $ordenamiento = new CSort();

            $ordenamiento->defaultOrder=array(


                'rif'=> array(


                    'desc' => 'CONCAT(`nacionalidad`, CAST(`rif` AS CHAR)) DESC',


                    'asc'=>'CONCAT(`nacionalidad`, CAST(`rif` AS CHAR)) ASC',


                    'default' => 'asc',


                ));


            $ordenamiento->attributes = array(


                'nombre_cliente'=>array(


                    'asc'=>'nombre_cliente ASC',


                    'desc'=>'nombre_cliente DESC',


                    ),


                'rif'=> array(


                    'asc'=>'CONCAT(`nacionalidad`, CAST(`rif` AS CHAR)) ASC',


                    'desc' => 'CONCAT(`nacionalidad`, CAST(`rif` AS CHAR)) DESC',


                    'default'=>'asc',


                    ),[/b]

and it works fine, thanks for the help, I appreciated.

But, I’ve another sort issue, that is, the third column in my cgridview called ‘agencia’, here is the code:

[b] array(‘name’=>‘id_agencia’,

                        'header'=>'Agencia',


                        'value'=>array($model,'desplegarAgencia'),


                        'htmlOptions' => array('id'=>'agencia'),


                        ),[/b]

the model’s method ‘desplegarAgencia’, here’s the code:

    protected function desplegarAgencia($data,$row)


    {


          $theCellValue=isset($data->id_agencia)?CHtml::encode($data->agencia->nombre_agencia):"desconocida";


            return $theCellValue;


    }

The relation is as follow (my name model is Clientes):

public function relations()

{


	return array(


                    .....


		'agencia' => array(self::BELONGS_TO, 'Agencias', 'id_agencia'),


                    .....


            );

Many ‘Clientes’ has one Agencia. The table Agencia has two fields:

id_agencia UNSIGNED PK AI NOT NULL

nombre_agencia VARCHAR(45) NOT NULL

My question is how I can sort this column by it’s name (‘nombre_agencia’) and not for it’s code (‘id_agencia’) what is happening.

Thanks again

9

Hi

I am willing to explain some things in order for you to learn from it. I think that you can solve your new problem with the same techniques. Replying on your new question would have to be paid support.

Other hint: any method that you call ‘getXXX’ can be addressed with the equivalent of $model->XXX (so you can avoid the array() stuff).

10

Thanks le_top, I’ll take your advice and try to figure out how to solve the sort problem.