Relational query incorrect SQL value?

Yii 1.1 General Discussion
1

I have 2 tables:


ACCOUNT

   ACCOUNT_ID (PK)

   PHONE_ID (FK relates to PHONE_ID in PHONE table below)

   etc...

      

PHONE

   PHONE_ID (PK)

   PHONE_NUMBER

In the Account model, I have the following relation defined:


'pHONE' => array(self::HAS_ONE, 'Phone', 'PHONE_ID'),

The application code performing the relational query:


   $account = Account::model()->findByPk(11);

   echo "PhoneId: $account->PHONE_ID<br>";

   if(null == $account->pHONE)

      echo "account->pHONE is null!!";

The results:

The SQL Yii generated for the initial ACCOUNT query:


system.db.CDbCommand.query(	 WITH USER_SQL AS (SELECT * FROM "ACCOUNT" "t" WHERE "t"."ACCOUNT_ID"=11), PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL) SELECT * FROM PAGINATION WHERE rownum <= 1)

The SQL Yii generated for the relational PHONE query:


system.db.CDbCommand.query(SELECT "pHONE"."PHONE_ID" AS "T1_C0", "pHONE"."PHONE_NUMBER" AS "T1_C1", "pHONE"."CREATE_DATE" AS "T1_C2", "pHONE"."LAST_UPDATE_DATE" AS "T1_C3" FROM "PHONE" "pHONE" WHERE ("pHONE"."PHONE_ID"=:ypl0). Bound with :ypl0='11')

Note in that the PHONE_ID field in the ACCOUNT table is 38, but in the SQL Yii generated to query the PHONE table, the PHONE_ID field is 11!!! To me it looks like Yii took the value from ACCOUNT_ID used in the inital query (11) and for some reason plugged it into the PHONE_ID value in the second query. This seems like a pretty simple exercise, so I must be missing something. Is it because I have a PHONE_ID field in both tables? If so, then how does the misapplied ACCOUNT_ID value play into it? Does anyone have any clues?

Thanks!

2

Never mind. Evidently, I’ve been mis-interpreting the relationships between the tables. It appears that ACCOUNT is a child of PHONE instead of the other way around.

I changed the relationship in the Account model to:


'pHONE' => array(self::BELONGS_TO, 'Phone', 'PHONE_ID'),

and added a relationship to the Phone model:


'aCCOUNT' => array(self::HAS_ONE, 'Account', 'PHONE_ID'),

Things seem to be working now.

Database table relationships seem backward to me for some reason. In terms of object oriented concepts, I’ve been looking at them as if the ACCOUNT table was instantiating an instance of the PHONE table, but really it is more like it is inheriting the PHONE table. I just wish I had figured this out before I spent 2 days stepping through SQL generation deep inside the CActiveFinder code…

3

Nice find…

Don’t be sorry for the time you spent analysing how Yii works… that is the best way to learn how Yii works… and in a long run makes you write better code…