CakePHP tables join help

Question

new to cakePHP and trying my first join. I've got one table called users and one called projects. one user can have many projects, so projects has a user_id column.

Here is my action in projects_controller:

function index() {

    $this->set('projects', $this->Project->find('all', array('joins' => array(
        array(
            'table' => 'users',
            'alias' => 'UsersTable',
            'type' => 'inner',
            'foreginKey' => false,
            'conditions' => array('UsersTable.id = Project.user_id')
        )
    ))));




}

Here is the SQL dump:

SELECT `Project`.`id`, `Project`.`title`, `Project`.`created`, `Project`.`website`, `Project`.`language_id`, `Project`.`user_id` FROM `projects` AS `Project` inner JOIN users AS `UsersTable` ON (`UsersTable`.`id` = `Project`.`user_id`) WHERE 1 = 1

As you will see everything seems fine except its not selecting anything from the users table but it is joining it.

And here is my view:

<table>
<tr>
    <th>Name</th>
    <th>User</th>
</tr>



<?php foreach ($projects as $project): ?>
<tr>
    <td>
        <?php echo $html->link($project['Project']['title'], array('controller' => 'projects', 'action' => 'view', $project['Project']['id'])); ?>
    </td>   
    <td>
        <?php echo $html->link($project['Project']['username'], array('controller' => 'users', 'action' => 'view', $project['Project']['user_id'])); ?>
    </td>   
</tr>
<?php endforeach; ?>

Have I messed up somewhere? the view attempts to list all projects along with the user who owns it.

Thanks alot,

Jonesy

Answer 1

Found out that what I wanted to be achieved by editing the projects model to:

var $belongsTo = array(
    'User' => array(
        'className'    => 'User',
        'foreignKey'    => 'user_id'
    )
); 

This did the trick!