Find condition over multiple Tables

I'm working on a problem with Cake's find.

I have following tables:

• projects (id, title, executive_id,...)
• users (id, username, role_id, email,...)
• roles (id, name)
• project_users (project_id, user_id)

Now i try to receive only the relevant projects for a certain user. There are different reasons that allow a user to view a project:

1) he is added to the project (entry in project_users)

2) he is the executive (projects->executive = $this->authUser['id'])

3) he is an admin (role_id =2)

This SQL-Query is working fine for my purpose:

SELECT DISTINCT p.title FROM projects as p, project_user as l, users as u, roles as r WHERE (p.executive_id = 4)OR (u.id=4 AND u.role_id=r.id AND r.name=Admin) OR (u.id= l.user_id AND l.user_id=4)

I've tried following code in the model, which is not working at all:

public function getRelatedProjects($user_id, $partner_id)
    {
      $conditions = array(
          'OR' => array(
              array('Project.executive_id' => $user_id),
              array(
                'AND' => array(
                    array('u.id' => $user_id),
                    array('u.role_id' => '2')
                  )
              ),
              array(
                'AND' => array(
                    array('u.id' => $user_id),
                    array('l.user_id' => 'u.id')
                  )
              )
          ),
          'AND' => array(
              array('Project.partner_id' => $partner_id)
          )
      );
     
      //$this->bindModel(array('hasOne' => array('User','Role','ProjectUser')));
     
      $result = $this->find('all', array(
        'fields' => array( 'DISTINCT(Project.id)', 'Project.title' ),
        'conditions' => $conditions,
           'joins' => array(
            array('table' => 'project_user',
                'alias' => 'l',
                'type' => 'LEFT',
                'conditions' => array(
                    'Project.id = l.project_id'
                )
            ),
            array('table' => 'users',
                'alias' => 'u',
                'type' => 'LEFT',
                'conditions' => array(
                    '1'
                )
            )
        )
      ));
     
      return $result;
    }

--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
 
---
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.