For example, posts table is in db1, categories table and category_post_join table are in db2. How to declare a many-to-many relationship between Post model and Category model?
'categories'=>array(self::MANY MANY, 'Category', 'category_post_join (post_id, category_id)')
works only if all tables are in the same database.
I don't think that's possible. The CActiveRecord functionality is based on a physical PDO connection, which is linked to a specific database. There is no support for doing a join across multiple database that I know of. I actually ran into the same problem a while ago and had to work around it.
I really hope there is a possible answer here, but I haven't found one so far.