company (id, ...)
region (id, company_id, ...)
division (id, region_id, ...)
user (id, division_id, ...)
A company has many regions, a region has many divisions and a division has many users.
Given a user, I want to find the company the user belongs to, so a simple sql query to find this would be
select company.* from company, region, division, user where company.id = region.company_id
AND region.id = division.region_id AND division.id = user.division_id AND user.id = <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />??;
HOWEVER what I need to know is:
Is it possible to define a relation in the User model to retrieve the related company.
Thus I can simply perform a query such as
User::model()->findByPk(1)->company
If its not possible to define this with a relation could it be done with a model method?
Got it to work by simply defining a method in the User model as follows, but really wanted to know if it could be done via a relation.
public function getCompany()
{
return Company::model()->findBySql(
'SELECT * from company,region,division,user
WHERE company.id = region.company_id
AND region.id = division.region_id
AND division.id = user.division_id
AND user.id = :id',array(':id'=>$this->id)
);
}
PS. In your example the order of the with tables doesn’t work as user doesn’t have a region relation. So needs to be