ambiguous column names in defaultscope

Yii 1.1 General Discussion
1

I have two ARs ("Board" and "Transaction"), each with a column "locale".


public function defaultScope()

{

    return array(

        'condition'=>"locale='".Yii::app()->language."'",

    );  

}

They have a "belongs to" relationship which is modeled in the relations methods:


'transaction' => array(self::BELONGS_TO, 'Transaction', 'transaction_id'),

Now, when I do


$boards = Board::model()->with('transaction')->findAll();

I get

“Integrity constraint violation: 1052 Column ‘locale’ in where clause is ambiguous.”

I know that I have to disambiguate the two columns, but I just don’t know how.

I tried


    return array(

        'condition'=>"transaction.locale='".Yii::app()->language."'",

    );

and


    return array(

        'condition'=>"board.locale='".Yii::app()->language."'",

    );

in the defaultScope, but that gives me a “Column not found: 1054 Unknown column ‘board.locale’ in ‘where clause’.” and might break other parts of the code where I’m loading the records without each other.

I read I have to set the alias property but I can’t seem to figure out how.

I’m sure it’s a simple solution but I can’t get it to work on my own. Your help is highly appreciated!

2

You can use the alias option in your CDBCriteria object: http://www.yiiframework.com/doc/api/1.1/CDbCriteria#alias-detail

The default value is "t" so in your case


public function defaultScope()

{

    return array(

        'condition'=>"t.locale='".Yii::app()->language."'",

    );  

}

should do the trick. And with a custom alias called "a"


public function defaultScope()

{

    return array(

        'alias'=>'a',

        'condition'=>"a.locale='".Yii::app()->language."'",

    );  

}
3

Thanks, seems to work with the first solution (custom alias does not seem to be necessary?)

4

No, it isn’t necessary, just wanted to point that one out in case you want to use something more “expressive” than “t” :)

5

Hmm, it now broke my code in another part of my application: Unknown column ‘t.locale’ in ‘where clause’.

Problem lies here now:


FROM `transaction` `transaction` WHERE (t.locale='de')

It seems there’s a difference when doing eager and lazy loading? (the first one was eager loading, this is now lazy loading). What can I do about this?

6

http://www.yiiframework.com/forum/index.php/topic/17595-column-disambiguation-on-named-scopes/

7

I faced the same problem.

The solution i found is to define the alias in the defaultScope definition. And that’s seems good (just coded it an hour ago, but all unit tests are ok).




        public function defaultScope() {

            return array( 'condition' => $this->tableName().'.active=1',

                          'alias' => $this->tableName()

                );

I used $this->tableName(), because that default scope is defined in a base class which my models use but you can set a string instead.

The only thing that change now is that you have to use the tableName (or the string you choose) and not the relation name when writing condition on related models.

That mean

News::model()->with( array(‘parent’) )->findAll(‘parent.category_id=:cat_id’, array(’:cat_id’ => $this->parent_id) );

becomes

News::model()->with( array(‘parent’) )->findAll(‘news_base.category_id=:cat_id’, array(’:cat_id’ => $this->parent_id) );

The change is in the findAll argument (‘parent’ (relation name) => ‘news_base’ (‘parent’ relation table).