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

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)


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.


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



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

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'),


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.

Ok I manage to do the search function

I added this line



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

GREAT! I finally managed to solved it.

I change

$criteria->group = "staff_id";


$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