Relational active record - not needed joins?

Archives Yii 1.0.x Discussions
1

Because I have 3 tables which are linked through non-indexed columns (I cannot change this, unfortunately) its very bad to do JOINS on other tables as you might know. So i must not use the together() - function of Yii.



$criteria = new CDbCriteria;


		$criteria->condition = 'TABLE_SCHEMA = :schema AND TABLE_NAME = :table';


		$criteria->params = array(


			'schema'=>$this->schemaName,


			'table'=>$this->tableName,


		);





$table = Table::model()->with(array(


			'columns',


			'indices',


		))->find($criteria);

This relational query is executed as follows:



SELECT TABLE.NAME, TABLE.SCHEMA, TABLE... FROM TABLES;





SELECT TABLE.NAME, TABLE.SCHEMA, COLUMN.* FROM TABLES


     LEFT OUTER JOIN COLUMNS ON (...);





SELECT TABLE.NAME, TABLE.SCHEMA, COLUMN.* FROM TABLES


     LEFT OUTER JOIN INDIZES ON (...);

There is always the JOIN of the (not needed) TABLES table. How can i get rid of this and perform non-JOIN statements like it would be done when you write the following code:



$tables = Table::model()->findAll($criteria);


$columns = Column::model()->findAll($criteria);


$indices = Index::model()->findAll($criteria);

Thanks for your suggestions…

2

I'm confused. Why would you call findAll() using with() if you don't want join?

3

I thought that this would be the more correct way to fit AR.

Is it (in this case) better to get these objects seperately?



$tables = Table::model()->findAll($criteria);


$columns = Column::model()->findAll($criteria);


$indices = Index::model()->findAll($criteria);
4

It depends on your needs. If you want to access columns of a specified table, using JOIN is better. If the columns and tables are accessed completely independently, you don't really need JOIN.

5

But using JOINS on non-indexed primary and foreign - key columns is VERY slow as you might know…

Therefore it is NO option for me to use the JOIN method (although that would seem to be more beautiful in terms of AR)

What I wanted to know is, if there is a method to avoid the QueryBuilder from building these queries with JOIN statements but as seperated queries like i described in the code of the previous post, and then to merge them together in PHP - code (Table->columns, Table->indizes)

But if that is not possible I can live with my solution, thanks for you hints  ;)

6

There is no such option in AR…