unable to sort by alias

Yii 1.1 General Discussion
1

I upgraded one of my websites to 1.1.7 and my dataprovider fails when sorting by alias. I have the following situation:


$dataProvider=new CActiveDataProvider('tName', array(

                        'criteria'=>array(

                            'select'=>'(id1+id2) as aliasA',

                            'join'=>'some joins',

                            'condition'=>'some condition',

                            'params'=>array(some params here),

                        ),

                        'sort'=>array(

                            'attributes'=>array('aliasA'),

                            'defaultOrder'=>'id ASC',

                            ),

			'pagination'=>array(

				'pageSize'=>self::PAGE_SIZE,

			),

		));

When I try to sort by aliasA it gives the me error: [error] [system.db.CDbCommand] CDbCommand::fetchAll() failed: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘t.aliasA’ in ‘order clause’.

Looking at the debug message from the log yii has generated the following order by ORDER BY t.aliasA

I pasted the sql in phpmyadmin and kept failing until I changed the order by to ORDER BY aliasA (without the t alias for the table) and it worked perfectly.

Is there a workaround this or a way to tell yii not to put t. infront of the order by field?

Cheers

2

noone?

3

Hi,

in CActiveDataProvider it says (on line 134) that sorting is going to use table alias setting from your model.

Maybe try setting table alias to ‘’ (an empty string) ?

You can also try sorting by




'sort'=>array(

    'attributes'=>array('(id1+id2)'),

    'defaultOrder'=>'id ASC',

),

didn’t try it though :)

4

I already tried sorting by ‘attributes’=>array(’(id1+id2)’), but didn’t work either. How do I set table alias to ‘’?

5

Try this




$tName=tName::model();

$tName->setTableAlias('');

$dataProvider=new CActiveDataProvider($tName, array(

                        'criteria'=>array(

                            'select'=>'(id1+id2) as aliasA',

                            'join'=>'some joins',

                            'condition'=>'some condition',

                            'params'=>array(some params here),

                        ),

                        'sort'=>array(

                            'attributes'=>array('aliasA'),

                            'defaultOrder'=>'id ASC',

                            ),

                        'pagination'=>array(

                                'pageSize'=>self::PAGE_SIZE,

                        ),

                ));

I forgot about CSqlDataProvider :)




$count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM '.tName::model()->tableName().')->queryScalar();

$sql='SELECT id1+id2 as aliasA FROM '.tName::model()->tableName();//apply all your criteria to this query

$dataProvider=new CSqlDataProvider($sql, array(

    'totalItemCount'=>$count,

    'sort'=>array(

        'attributes'=>array('aliasA'),

        'defaultOrder'=>'id ASC',

    ),

    'pagination'=>array(

        'pageSize'=>self::PAGE_SIZE,

    ),

));

hope it helps,

cheers

6

Hi

Thanks for your reply. The first approach is not working. The CSqlDataProvider approach seems to not throw the error when I attempt to sort but I am unable to render the value of the aliases through $data->someValue when I render the dataProvider in my view. I wonder if I am missing anything?

Cheers,

bettor

7

I’ve been able to get this to work with CSqlDataProvider. Thanks so much for your help