Manual JOIN on CakePHP

Question

I'm trying to do manual join because I expected to get better query result than CakePHP did.

This is my controller's coding

$this->ClinicWishList->contain();
$this->set('recordSets', $this->ClinicWishList->find('all', array(
  'fields' => array('ClinicWishList.*', 'ApplicantIssue.issue_desc', 'User.nickname', 'User.role'),
  'joins' => array(
    array(
      'table' => 'applicant_issues',
      'alias' => 'ApplicantIssue',
      'type' => 'left outer',
      'foreignKey' => false,
      'conditions' => array('ClinicWishList.issue_id' => 'ApplicantIssue.id')
    ),
    array(
      'table' => 'users',
      'alias' => 'User',
      'type' => 'left outer',
      'foreignKey' => false,
      'conditions' => array('ApplicantIssue.user_id' => 'User.id')
    )),
    'conditions' => array('ClinicWishList.user_id' => $this->Auth->user('id')),
    'order' => 'ClinicWishList.created DESC'
  )));

CakePHP produced the SQL like this.

enter image description here

There is no error but I got empty result from the joined table like this.

enter image description here

I copied the SQL and tried to execute on the GUI tool, I got this warning.

enter image description here

How to modify coding in CakePHP to get SQL like

enter image description here

instead of this

enter image description here

Please help, thanks.

score 0 · Accepted Answer · answered Jul 05 '13 at 05:14

You should change this line:

'conditions' => array('ClinicWishList.issue_id' => 'ApplicantIssue.id')

to:

'conditions' => array('`ClinicWishList`.`issue_id` = `ApplicantIssue`.`id`')

and change this line:

  'conditions' => array('ApplicantIssue.user_id' => 'User.id')

to:

  'conditions' => array('`ApplicantIssue`.`user_id` = `User`.`id`')

Manual JOIN on CakePHP

1 Answers1