CakePHP tables join help

Question

Hi folks,

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

Hello Jonesy,

the output, you mentioned in your question, means, that you succeeded in setting up a join, but not yet set up the selection condition. Here you can find how to set up the missing second condition.

Kind regards, Benjamin.

Answer 2

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!