Struggling with RELATIONS

Can anyone suggest how to solve the following

I have the following relations


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?

Any help would be appreciated.

amc

Configure the relations in the respective models like this:


user BELONGS_TO division

division BELONGS_TO region

region BELONGS_TO company



See the guide on AR relations for details.

Then you can fetch your data like this:


$user=User::model()->with('region.division.company')->findByPk(1);

if ($user!==null)

  $company=$user->region->division->company;

Thanks Mike,

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




user=User::model()->with('division.region.company')->findByPk(1);



amc