Cross-database relations: join table cannot be found

I’m developing a Yii 1.1.2 application in which one of the tables is in a separate database to the rest. Relations to the model for this table work as expected, once I specified the details in the model:


class Wombat extends CActiveRecord

{

...

    public function tableName()

    {

        return 'dbname.prefix_wombat';

    }

}



and the foreign key in other models like so:




class Foo extends CActiveRecord

{

...

    public function relations()

    {

        return array(

            'wombats' => array(self::MANY_MANY, 'Wombat', 

                '{{wombat_foo}}(foo_id, wombat_id)',

                'select' => 'col1, col2, col3, col4',

            ),

        );

    }

}



This allows me to use:




$model = $this->loadModel();

$wombats = $model->wombats;

etc. in controllers. However, when I attempt the reverse I run into trouble:


class Wombat extends CActiveRecord

{

...

    public function relations()

    {

        return array(

            'foos' => array(self::MANY_MANY, 'Foo', 

                'yiidbname.yiiprefix_wombat_foo(wombat_id, foo_id)'),

        );

    }

}



The relation "foos" in active record class "Wombat" is not specified correctly: the join table "yiidbname.yiiprefix_wombat_foo" given in the foreign key cannot be found in the database.

Now, I checked the query it issued using the Yii debug bar and it’s perfectly formed, works fine when sent to the database. It’s of this ilk:


Querying SQL: SHOW COLUMNS FROM `yiidbname`.`yiiprefix_wombat_foo`

If anyone has seen anything like this before or has suggestions, I’d love to hear 'em.

Never mind. As usual, in the act of posting found the solution: database permissions problem. The user for one db had no permissions for the other!

Thanks for posting anyway, I’m copying your solution just now for a sad and bad situation in which I cannot avoid cross-db query