How to reference a table inside CDbCriteria's condition?

Yii 1.1 General Discussion
1

Here:


$events = new CActiveDataProvider('EventLog', array(

  'criteria' => array(

    'condition' => 'isVisible = 1 AND ( type <> 7 OR t.idUser <> 99 ) ',

    'order' => 'timeStamp DESC',

    'with' => array('User.Profile') ) ) );

The only way I found to reference the table is with t which is the SQL alias Yii uses internally.

Neither EventLog (model name) nor tbl_eventLog (table name) worked.

How can I achieve this other than with t?

2

CDbCriteria->alias

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#alias-detail

~thinkt4nk

3

If you use a model name, the table refered to the model is within the tableName() function (in brackets {{table}} when you use table prefix (main.php config file)

If you wish to access the table name of a model, other than t I use: {{table}} within CDbCommands or with composite conditions: ‘condition’=>'t.id IN (SELECT field1.id FROM {{modeltablename}}) in CDbCriteria classes.

NOTE: you dont need to use brackets if your table does not have prefixes (even with them, it is actually made in case you change the prefix you dont need to go throughout all the models to change their table names, just by changing the tableprefix in main.php will do it)

Hope it helps

4 

'condition' => 'isVisible = 1 AND 

 ( type <> 7 OR {{fc_eventLogs}}.idUser <> '.$this->logged_user->idUser.' ) '

Tried it, but doesn’t work:


Syntax error or access violation: 1064 You have an error in your SQL syntax; 

check the manual that corresponds to your MySQL server version for the right syntax to use near

'{fc_eventLogs}}.idUser <> 3 ) )' at line 1

PS: thinkt4nk: Ok, that way I get the alias of the main table, which is the same as using t, but as there are 3 tables joined here, how can I reference the other two?

5

is fc_ your table prefix? then you shouldn’t place that there it should be {{eventlogs}} otherwise not

main table referenced as ‘t’, the rest if you are using table prefixes just {{modelname}} otherwise just place the name of the table

6

Our tables are prefixed, but Yii is not aware of that. At least we didn’t configure it specially for prefixes.

Anyway, tried it and didn’t work either.

7

Have a look at this section in the guide.

/Tommy

8

Thanks! that’s it!

For others with the same problem:

9

Can the alias of a relation be renamed for a CDbCriteria::with ?

10

You can change the alias this way




...->with(

  'somerelation'=>array(

    ...

    'alias'=>'somealias',

  )

)->...

Thus probably works with an explicit CDbRelation object. The latter not tested, though.

/Tommy