DB order clause problem

Hi all,

I am having the following issue with my blog tutorial:

In my relations() method in the Post model I have the following relation declaration (copied directly from the tutorial):

return array(

            'author'=>array(self::BELONGS_TO, 'User', 'authorId'),

            'comments'=>array(self::HAS_MANY, 'Comment', 'postId',

            'order'=>'comment.createTime'),

            'tagFilter'=>array(self::MANY_MANY, 'Tag', 'PostTag(postId, tagId)',

                'together'=>true,

                'joinType'=>'INNER JOIN',

                'condition'=>'??.name=:tag',

            ),


		);

Yii does not recognize this part:

'order'=>'comment.createTime'),

and throws the following error:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘comment.createTime’ in ‘order clause’

I can confirm that I have table name comment and there is createTime field in that table. However, if I change that line with the following it seems to work:

'order'=>'??.createTime'),

I saw these question marks used in another topic in this forum (not related to this problem) so decided to test it. Can someone explain how exactly Yii understands the question marks and how they should be used correctly?

I have no idea and hope that someone can help me understand why this is happening. Here is the rest of the error page:

Have look here:

http://www.yiiframework.com/doc/guide/database.arr

Go to the section "Disambiguating Column Names" halfway down the page.

/Tommy

From what I saw

'order'=>'comment.createTime'),

should work like this

'order'=>'comments.createTime'),

According to: http://www.yiiframework.com/doc/guide/database.arr#disambiguating-column-names I tried to use an order for my data retrieval.

At first, I used the following code, leading to an error:

public function getSubCategoriesOptions2($parentID=null) {

if(!is_null($parentID)) {

 $criteria=array(

 'condition'=>'categoryID='.$parentID,

 //'order'=>'SubCategories.name ASC',

);                     

$values = $this->with('category')->findAll($criteria);

return CHTML::listData($values, 'id', 'name');

} else {

 $criteria=array(

 'order'=>'SubCategories.name',); // <-- HERE IS THE PROBLEM

 $values = $this->with('category')->findAll($criteria);

 return CHTML::listData($values, 'id', 'name','category.name');}

}

The SQL Statement, Yii constructed from the input above, is:

[sql]

SELECT

"SubCategories"."id" AS "t0_c0", 


"SubCategories"."categoryID" AS "t0_c1", 


"SubCategories"."name_en" AS "t0_c2", 


"SubCategories"."name_de" AS "t0_c3", 


"SubCategories"."name" AS "t0_c4", 


t1."id" AS "t1_c0", 


t1."name_de" AS "t1_c1", 


t1."name_en" AS "t1_c2", 


t1."name" AS "t1_c3" 

FROM

"SubCategories" 

LEFT OUTER JOIN

"Categories" t1 ON ("SubCategories"."categoryID"=t1."id") 

ORDER BY SubCategories.name

[/sql]

The problem is, that at least "SubCategories" has to be in quotation marks, to get the sql query to work. It should be ORDER BY "SubCategories".name.

Providing this information in my PHP code, the query works:

        

public function getSubCategoriesOptions2($parentID=null)

	{

		if(!is_null($parentID)) {

                    $criteria=array(

                            'condition'=>'categoryID='.$parentID,

                            //'order'=>'SubCategories.name ASC',

                    );

                    $values = $this->with('category')->findAll($criteria);

                    return CHTML::listData($values, 'id', 'name');


                } else {

                   $criteria=array(

                        'order'=>'"SubCategories".name',// <-- NOW IT WORKS

                   );

                   $values = $this->with('category')->findAll($criteria);

                   return CHTML::listData($values, 'id', 'name','category.name');

                }

	}

If I want to order with more than one column, I have to use t1…tn instead of the real table names. For example all the following lines of source code

'order'=>'category.name, "SubCategories".name'

'order'=>'"category".name, "SubCategories".name'

'order'=>'"Categories".name, "SubCategories".name'

lead to an error.

The only way i was able to get it working is using

'order'=>'t1.name, "SubCategories".name'

I guess, that this should not be the expected behavior of Yii?

Greetz,

Bergtroll