LEFT JOIN on the same table

Hi,

the following SQL query worked perfectly with PRADO's findAllBySql method, but doesn't anymore with Yii as CActiveRecord complains that the property "Role.InheritingRoleName" is not defined.

SELECT


	Role.*,


	inherited.Name AS InheritingRoleName


FROM


	Role


LEFT JOIN


	Role AS inherited


ON


	inherited.Id = Role.InheritingRoleId


ORDER BY


	inherited.InheritingRoleId ASC,


	Role.Id ASC

Therefore i need an example on how to do a LEFT JOIN (with one field only) on the very same table using relational active record and/or a method which gets this SQL query working with findAllBySql.

Greetings from Hamburg / Germany

  • rojaro -

Declare a property named inheritingRoleName in ur AR class.

Although that helps get to rid of the error message, it doesn't solve the problem, as the value retrieved from the database is never assigned to that property.

Ah, I see. Fixed.

Could you also give the error call stack before you added the inheritingRoleName property? I was curious why an error would appear. Thanks.

Sure, here it is:

#0 /var/www/localhost/library/yii/framework/db/ar/CActiveRecord.php(393): CComponent->__get('InheritingRoleN...')


#1 /var/www/pf.dev/htdocs/protected/components/AclManager.php(58): CActiveRecord->__get('InheritingRoleN...')


#2 /var/www/pf.dev/htdocs/protected/components/AclManager.php(89): AclManager->_setupAclCache(Object(CApcCache))


#3 /var/www/localhost/library/yii/framework/core/CApplication.php(713): AclManager->init()


#4 /var/www/localhost/library/yii/framework/core/CApplication.php(801): CApplication->getComponent('acl')


#5 /var/www/localhost/library/yii/framework/core/CApplication.php(122): CApplication->preloadComponents()


#6 /var/www/localhost/library/yii/framework/core/CApplication.php(108): CApplication->init()


#7 /var/www/localhost/library/yii/framework/YiiBase.php(74): CApplication->__construct('./protected/con...')


#8 /var/www/pf.dev/htdocs/index.php(7): YiiBase::createWebApplication('./protected/con...')


#9 {main}

However, since i saw in the guide that relational active record also supports joins, i still really would like to know whether it is possible to do this query from my first post in this thread using plain relational active record instead of having to use findAllBySql.

Greetings from Hamburg / Germany

  • rojaro -

Yes, you can do this using relational AR.

There is a unit test for this scenario in the Yii SVN.

Please refer to /tests/ut/framework/db/ar/CActiveRecordTest.php and /tests/ut/framework/db/data/models.php about class "Category".