Yii Relation Model (One To Many And Many To Many)

Yii 1.1 General Discussion
1

Hello, I have a situation here.

Here are the 3 tables extracted from my database, (staff, resource_management, project)

  1. staff (id, name)

  2. resource_management (staff_id, project_id, date_transfer)

  3. project (id, code, end_date)

Relation

1 and 2 (one to many)

2 and 3 (many to one)

Here is what I want my cgridview to look like

distinct(Staff.id) , staff.name, max(project.end_date), project.code, resource_management.date_transfer

This is what I have currently in my staff model.


                      'transfer'=>array(self::HAS_ONE,'ResourceManagement','staff_id','alias'=>'i','select'=>'*',

                                                'join'=>'LEFT JOIN(select max(date_transfer) as max FROM resource_management GROUP BY staff_id) j ON i.date_transfer = j.max'),

                    

'projects'=>array(self::HAS_ONE,'Project','project_id','through'=>'transfer'),

The above code is getting the max date_transfer but how do I get the max(end_date) for project ?

2

This is how i solve it


                        'transfer'=>array(self::MANY_MANY,'Project','resource_management(staff_id,project_id)','alias'=>'i','order'=>'i.end_date DESC', 'limit'=>'1'),

                        'project'=>array(self::HAS_MANY,'Project','id','through'=>'transfer'),

Everything displayed as what I wanted but the search can’t seems to work properly. It gives the wrong result and the total pagination varies from page to page which is incorrect. So how do I solve this?

What should I add to my search function so that when I search it will be able to display the result correctly.

3

Ok I manage to do the search function

I added this line


'transfer'=>array('together'=>true)

and


$criteria->group = "staff_id"

But there is still a problem. The staff who doesn’t have any project or any records

in resource_management then it will not show.

4

GREAT! I finally managed to solved it.

I change


$criteria->group = "staff_id";

to


$criteria->group = "t.id";

I do know that this is a bit confusing to pick up what I wrote but I do hope that it will be helpful to someone