CakePHP 2: Multiple level join in Model Association

Question

Let's say I have 3 models.

User (users of the system)
Applicant (More details on each user such as address, phone, interested_position, etc)
Position (In table applicants I only store interested position as position_id, this is where the position's description)

This is my model php files.

class User extends AppModel { 
  public $hasOne = 'Applicant'; 
}


class Applicant extends AppModel { 
  public $belongsTo = array('User', 'Position'); 
}


class Position extends AppModel { 
  public $hasMany = 'Applicant';
}

This is my UsersController.php

class UsersController extedns AppController {
  public function index() {
    $this->User->find('all');
  {
}

When execute /users/index I got SQL statement on SQL dump as follow.

select *
from users
  left outer join applicants
    on users.id = applicants.user_id

But this is what I need.

select *
from users
  left outer join applicants
    on users.id = applicants.user_id
  left outer join positions
    on positions.id = applicants.position_id

So what to do in CakePHP to get this kind of result.

Thanks all.

Chris Chris · Accepted Answer · 2013-04-24T12:33:49

I see two options: Using the recursive attribute of a find to include a model's association's associations: http://book.cakephp.org/2.0/fr/models/model-attributes.html#recursive

Using the containable behavior to do the same trick but with more control on the joins made in the request: http://book.cakephp.org/2.0/fr/core-libraries/behaviors/containable.html

The first solution is very simple, just add 'recursive' => 2 to your find('all'), the second solution is a bit more lengthy but has much much better performances.

CakePHP 2: Multiple level join in Model Association

1 Answers