CakePHP 1.3 associations

Question

I've started writing a project using CakePHP (1.3) and am finding it difficult to return results using the CakePHP style of querying.

The project is an issue tracker. So far I only have the following tables - 'users', 'projects' and a link table 'projects_users' because any users can be associated with one or more projects. There is also a 'statuses' table linked to the projects table.

I have set up the associations in the models.

I am trying to return a summary page for those logged in displaying the project name, the status of the project filtered by the logged in user_id. If I did this in SQL then I would do something like the following.

SELECT
  `statuses`.name,
  `projects`.name
FROM
  `statuses`
  INNER JOIN `projects` ON (`statuses`.id = `projects`.status_id)
  INNER JOIN `projects_users` ON (`projects`.id = `projects_users`.project_id)
  INNER JOIN `users` ON (`projects_users`.user_id = `users`.id)
WHERE
  `users`.id = 1

$this->Project->find('all')) returns all projects, status details and user details.

    Array
    (
        [0] => Array
            (
                [Project] => Array
                    (
                        [id] => 1
                        [name] => Project 1
                        [status_id] => 2
                        [date_created] => 0000-00-00
                    )

                [Status] => Array
                    (
                        [id] => 2
                        [name] => Live
                    )
                [User] => Array
                    (
                        [0] => Array
                            (
                                [id] => 1
                                [username] => d
                                [password] => ********************************
                                [role] => master
                                [ProjectsUser] => Array
                                    (
                                        [id] => 1
                                        [project_id] => 1
                                        [user_id] => 1
                                    )
                            )
                    )
            )

Etc..

I have also tried

$this->Project->find('all', array('conditions'=>array('User.id' => $this->Session->read('Auth.User.id'))))

which returns an error:

Unknown column 'User.id' in 'where clause'

If I try

$this->Project->User->find('all', array('conditions'=>array('User.id' => $this->Session->read('Auth.User.id'))))

it correctly returns all projects associated with the user_id, but now I am no longer returning the value of status, just the id.

Array
(
    [0] => Array
        (
            [User] => Array
                (
                    [id] => 1
                    [username] => d
                    [password] => ********************************
                    [role] => master
                )

            [Project] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [name] => Project 1
                            [status_id] => 2
                            [date_created] => 0000-00-00
                            [ProjectsUser] => Array
                                (
                                    [id] => 1
                                    [project_id] => 1
                                    [user_id] => 1
                                )
                        )
                    [1] => Array
                        (
                            [id] => 3
                            [name] => Project 3
                            [status_id] => 1
                            [date_created] => 0000-00-00
                            [ProjectsUser] => Array
                                (
                                    [id] => 2
                                    [project_id] => 3
                                    [user_id] => 1
                                )
                        )
                )
        )
)

Is there a way to easily return what I'm after the 'CakePHP' way? I'm sure I'm missing something obvious here.

Answer 1

which returns an error - Unknown column 'User.id' in 'where clause'

Your table name is users. Plural. You passed it something not users.

Answer 2

You want something like this:

$this->Project->User->find(
    'all',
    array(
        'conditions' => array(
            'User.id' => $this->Session->read('Auth.User.id')
        ),
        'contain' => array(
            'Project' => array(
                'Status'
            )
        )
    )
);

I might have your relations wrong but that should do the trick. It's containable that you want. containable is a core library, but will not join. If you really want joins there is a linkable behavior that Google can find for you.