Yii Framework Forum: LEFT JOIN on the same table - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

LEFT JOIN on the same table Rate Topic: ***** 1 Votes

#1 User is offline   rojaro 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 29
  • Joined: 06-October 08
  • Location:hamburg.de

Posted 12 October 2008 - 11:57 AM

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 -
0

#2 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,876
  • Joined: 04-October 08
  • Location:DC, USA

Posted 12 October 2008 - 12:32 PM

Declare a property named inheritingRoleName in ur AR class.
0

#3 User is offline   rojaro 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 29
  • Joined: 06-October 08
  • Location:hamburg.de

Posted 12 October 2008 - 02:17 PM

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.
0

#4 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,876
  • Joined: 04-October 08
  • Location:DC, USA

Posted 12 October 2008 - 02:39 PM

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.
0

#5 User is offline   rojaro 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 29
  • Joined: 06-October 08
  • Location:hamburg.de

Posted 12 October 2008 - 02:59 PM

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 -
0

#6 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,876
  • Joined: 04-October 08
  • Location:DC, USA

Posted 12 October 2008 - 08:16 PM

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".
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • This topic is locked

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users