Hi…
I’m having a bit of an issue with a relation that I’m hoping you can please assist with!
I have a table of people:
[font="Courier New"]project_people
-
person_id
-
project_id
-
project_role
[/font]
which used to store ‘project_role’ as a text string from another table. Made things very easy as there was always one lead that could be referred to directly as ‘Project Lead’.
eg within projects:
public function relations() {
return array(
'projectLeads' => array(self::HAS_MANY, 'ProjectPeople', 'PROJECT_ID',
'condition' => 'projectLeads.project_role="Project Lead"'),
);
}
Unfortunately things are now more complicated than they used to be and I’ve had to break this out into:
[font="Courier New"]project_type_roles
-
project_type_id
-
project_role_id
-
is_lead (1/0)
[/font]
Meaning that if I want to determine the list of project leads from a given project I now need to:
-
Get Project Type (project.type_id)
-
Determine which role is marked as ‘is_lead’ for that project type (project_type_roles.is_lead)
-
Find out which text string that project_role_id corresponds with (project_roles.project_role)
-
Check for that role in the relationship (project_people.project_role)
Is it possible to still do this from within a relation?
What’s the best way to go about this?
Connections this deep are already starting to slow down things significantly…
Advice greatly appreciated!
As the guide sais, you can use ‘with’ as a relation parameter. Just add a second relation to the “project_type_roles” table and refer to it in the condition. Remember to disable eager loading by adding a ‘together’ criteria somewhere in the ‘with’ param.
Thanks!
Yes!..
I managed to create a relation for ‘projectLeadRole’ that returns the numeric id of the lead… but what I have stored in project people is the text string equivalent of that role id… I need to be able to get to the project lead from within the project model, but am still having trouble defining that (see bottom relation):
public function relations()
{
return array(
'projectTypeRoles'=> array(self::HAS_MANY, 'ProjectTypeRoles', array('project_type_id'=>'type_id')),
'projectLeadRole'=> array(self::HAS_ONE, 'ProjectTypeRoles', array('project_type_id'=>'type_id'),
'on'=>'is_lead="1"',
),
'projectLead' => array(self::HAS_, 'ProjectPeople', 'project_id',
'with'=>array('projectRole','project'),
'on'=>'projectRole.project_role_id=(SELECT project_role_id FROM project_type_roles WHERE project_type_roles.project_type_id=project.type_id AND is_lead=1)',
),
);
}
Obviously this sub select statement is not putting the give project (t) type_id into the statement and is therefore failing…
The following does work:
public function relations()
{
return array(
'projectLead' => array(self::HAS_MANY, 'ProjectPeople', 'project_id',
'on'=>'projectLead.project_role IN (SELECT DISTINCT project_role
FROM project_type_roles INNER JOIN project_role ON project_type_roles.project_role_id = project_role.project_role_id WHERE is_lead = 1)',
);
}
But produces such a slow mysql query that it’s really not worth running…
There must be a better way?
