how to get count of related records in different database

Archives Yii 1.0.x Discussions
1

Hi,

I have two tables (table_a, table_c) in two different databases (db_a, db_c). I’ve overriden getDbConnection in the model for each table and I’m able to retrieve records just fine. I’ve set up relations between the two models.

But, in the controller for table_a, I’d like to be able to get the count of related records in table_b so I can print out a table saying how many table_b records there are for each table_a record.

the way I tried to do this was to have this in the table_a controller’s list method:




$models=table_a::model()->with('table_c')->findAll($criteria);

thinking I could then get $models->table_c->count() in the list view.

Unfortunately, I’m getting the following error:




CDbCommand failed to execute the SQL statement: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db_a.table_c' doesn't exist

so… apparently it’s looking for table_c in the same database as table_a, even though table_c has the correct database connection set in its getDbConnection method.

Is there any way around this? How can I get at the records of a related table that’s in another database?

-Charlie

2

I’d say if you really need this to work now, get the results from one request, loop through them to effect the second request in the other DB. Expensive, but it will work.

3

I’ve clarified a few things for myself here - the two tables are located in different schemas/databases on the same server within the same mysql instance…

so I don’t need a different connection to the database, instead, I simply need to qualify the table name in the model with the schema name, for example, in the model for table_c, return schema name in the tableName method:

public function tableName()


{


	return 'db_c.table_c';


}

and now I can join to it from any table in db_a.

so - it’s possible to work with tables in multiple schemas/databases within the same query.