[Solved]Order By Column 1 And 2 When Click Column 1

Yii 1.1 General Discussion
1

Good evening.

I’ve got a CGridView with 2 columns (column 1 is a foreign key).

I want when I click column 1, the CGridView is sorted by column 1 and then by column 2.

My model:


public function search()

{

.....


return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

                        'sort'=>array(

                            'defaultOrder'=>'FK.A, B ASC',  

                            'attributes'=>array(

                                'FK'=>array(

                                    'asc'=>'FK.A',

                                    'desc'=>'FK.A DESC',

                                ),

                                'B'=>array(

                                    'asc'=>'B',

                                    'desc'=>'BDESC',

                                ),

                            ),

                        ),

		));


.....

}

DefaultOrder is OK, but not click column order.

Any idea?

Thanks.

2

Something like this I believe:




return new CActiveDataProvider($this, array(

   'criteria'=>$criteria,

   'sort'=>array(

      'defaultOrder'=>'FK.A, B ASC',

      'attributes'=>array(

         'FK'=>array(

            'asc'=>'FK.A, B', //here

            'desc'=>'FK.A DESC, B DESC', //and here

         ),

         'B'=>array(

            'asc'=>'B',

            'desc'=>'BDESC',

         ),

      ),

   ),

));
3

Hi compact_corpse and thanks for your reply, but i didn’t resolv the problem.

Whatever you write I had already tested, and following occurs:

  • If I click on column 1, only column 2 is sorted.

I tried several thing but I can’t get it to work.

Any other idea?

Thanks.

4

Hi again!

I have this in model search:


$sort=new CSort;

...

                $sort->defaultOrder='NameForeignKey.NameField1, NameField2 ASC';

                $sort->attributes=array(

                    'NameField1'=>array(

                        'asc'=>'NameForeignKey.NameField1',

                        'desc'=>'NameForeignKey.NameField1 DESC',

                    ),

                    'NameField2 '=>array(

                        'asc'=>'NameField2 ',

                        'desc'=>'NameField2 DESC',

                    ),

                );

...


return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

                        'sort'=>$sort,

		));


...

When I click on column "NameField1", CGridView is only sorted by this column, and if I click on column "NameField2", CGridView is only sorted by column "NameField2".

Then, I modify search model by this:


$sort=new CSort;

...

                $sort->defaultOrder='NameForeignKey.NameField1, NameField2 ASC';

                $sort->attributes=array(

                    'NameField1'=>array(

                        'asc'=>'NameForeignKey.NameField1, Namefield2',

                        'desc'=>'NameForeignKey.NameField1, NameField2 DESC',

                    ),

                    'NameField2 '=>array(

                        'asc'=>'NameField2,NameForeignKey.NameField1',

                        'desc'=>'NameField2,NameForeignKey.NameField1 DESC',

                    ),

                );

...


return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

                        'sort'=>$sort,

		));


...

And when I click on column “NameField1”, CGridView is only sorted by column “NameField2”, and if I click on column “NameField2”, CGridView isn’t ordered.

$sort->defaultOrder works fine!!!!!

Any idea?

Regards.

5

Hi,

you can try to debug with bad parameter, it should show the database request which you can use in SQL for testing.

For instance adding a field named toto which didn’t exist




                    'NameField1'=>array(

                        'asc'=>'NameForeignKey.NameField1, Namefield2, toto',

                        'desc'=>'NameForeignKey.NameField1, NameField2, toto DESC',

                    ),

                    'NameField2 '=>array(

                        'asc'=>'NameField2,NameForeignKey.NameField1, toto',

                        'desc'=>'NameField2,NameForeignKey.NameField1, toto DESC',

                    ),
6

Here’s the SQL statement:


SELECT 

`t`.`Id1` AS `t0_c0`, 

`t`.`Id2` AS `t0_c1`, 

`t`.`Field1` AS `t0_c2`, 

`NameForeignKey`.`Id2` AS `t1_c0`, 

`NameForeignKey`.`Field2` AS `t1_c1` 

FROM 

`Table1` `t`  

LEFT OUTER JOIN `Table2` 

`NameForeignKey` ON (`t`.`Id2`=`NameForeignKey`.`Id2`)  

ORDER BY Field1, NameForeignKey.Field2 

LIMIT 10

I tried it and it doesn’t work in MySQL Workbench.

I think the sentence is OK.

I tried another sentence:


select a.field1, b.field2 from table1 as a, table2 as b where 

a.id1=b.id1 order by a.field1, b.field2 desc

And doesn’t work neither.

I don’t understand nothing.

[size="6"]SOLVED[/size]

I had an error in SQL sentence (What a shame!):


select a.field1, b.field2 from table1 as a, table2 as b where 

a.id1=b.id1

[color="#FF0000"]order by a.field1 asc, b.field2 asc[/color]


$sort->attributes=array(

                    'Field1'=>array(

                        'asc'=>'ForeignKey.Field1 ASC, Field2 ASC',

                        'desc'=>'ForeignKey.Field1 DESC, Field2 ASC',

                    ),

                    'Field2'=>array(

                        'asc'=>'Field2 ASC, ForeignKey.Field1 ASC',

                        'desc'=>'Field2 DESC, ForeignKey.Field1 ASC',

                    ),

                );

Thanks and greetings to all!