Relations Angst - Adding Complexity


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


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)


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.



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



      'projectLead' => array(self::HAS_, 'ProjectPeople', 'project_id',


					'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?