Hi,
I’ve found an undocumented case in CActiveRecord / CActiveFinder. I need to join two models in a CDbCriteria, but not on a PK/FK relation. For example, we have two models/tables, table1 and table2. The query looks like
SELECT * FROM table1 AS t LEFT JOIN table2 AS t2 ON t2.id = (SELECT id FROM table2 AS inner WHERE [...]);
The full example is not really matter, the business logic requires this form. But clearly visible, this is not a standard PK/FK join.
I found out that it only works in Yii if I set up the relation in this way in Table1 model:
'table2' => array(self::HAS_ONE, 'Table2', '', 'on' => 't2.id = (SELECT id FROM table2 AS inner WHERE [some query logic]')
So the PK is empty. In the other hand, to get the correct results from the query, the relation’s name MUST BE the same as the related table’s name. I didn’t traced it yet, why it works in this way, but before I start to dig into the library code, first I want to ask your opinions. If the PK is not empty, the query works as documented, so the ON statement will be added with an AND operator, but it breaks my query logic.
So my question is, is this a feature or a bug? If it is a feature, could it be more documented and possibly more usable with some improvements, like not restricted relation names?
If it is a bug, could the relations be more flexible, like the option to chose if you want to use a PK or not, or append the ON clause with OR, not only with AND, or forcibly override the default ON statement? I know these are error prone with bad statements, but complex SQL queries needs some experience anyway, so I don’t see a real issue with this.
Server, OS, Browser doesn’t matter, the DB is MySQL, Yii version 1.1.14.