CakePHP: Joined Table Data Not Displaying in View

Question

I have two Models linked in a hasMany/belongsTo relationship. Here is the hasMany definition:

//Table hr_emp_ids. Each employee can have many HR cases.
public $hasMany = array(
    'HrCase' => array(
        'className' => 'HrCase',
        'foreignKey' => 'emp_user_id'
        )
    );

And here is the belongsTo definition:

//Table hr_cases. Each HR case is owned by an employee.
public $belongsTo = array(
    'HrEmpId' => array(
        'className'=> 'HrEmpId',
        'foreignKey' => 'emp_user_id'
    );

The Controller for my view is dead simple:

public function view($id = null) {      
    $this->HrCase->id = $id;
    if (!$this->HrCase->exists()) {
        throw new NotFoundException(__('Invalid Case ID'));
    }
    $options = array('conditions' => array('HrCase.' . $this->HrCase->primaryKey => $id));
    $this->set('case', $this->HrCase->find('first', $options));
}

All I'm trying to do is display the hire_date and ssn from the hr_emp_ids table based on hr_cases.emp_user_id = hr_emp_ids = emp_user_id. Here is the View code:

<tr>
<td><strong>Employee: </strong><br><?php echo h($case['HrCase']['full_name']); ?></td>
<td><strong>Date of Hire: </strong><br><?php echo h($case['HrEmpId']['hire_date']); ?></td>
<td><strong>SSN: </strong><br><?php echo h($case['HrEmpId']['ssn']); ?></td>

Table structures:

desc hr_emp_ids;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| hire_date   | date        | YES  |     | NULL    |                |
| ssn         | varchar(11) | NO   |     | NULL    |                |
| emp_user_id | int(11)     | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

desc hr_cases; (truncated)
+--------------------+-------------+------+-----+---------+----------------+
| Field              | Type        | Null | Key | Default | Extra          |
+--------------------+-------------+------+-----+---------+----------------+
| id                 | int(11)     | NO   | PRI | NULL    | auto_increment |
| emp_user_id        | int(11)     | NO   |     | NULL    |                |

Nothing from the HrEmpId model will display. I'm not sure what I'm doing wrong here. I've made associations like this dozens of times with no trouble. What might I be missing?

bill bill · Accepted Answer · 2016-07-21T15:47:30

I don't see any immediate reason why the associated data isn't showing. Your find code here

$options = array('conditions' => array('HrCase.' . $this->HrCase->primaryKey => $id));
$this->set('case', $this->HrCase->find('first', $options));

looks like it could be simplified by using getById(), because the only condition in $options is checking the $id. It's worth adding the Containable Behavior to the HrCase model and then setting the second argument of getById() to true. This should allow you to specify exactly what associated data is returned.

Edit:

In the HrCase model you are setting up a $belongsTo relationship with the foreign key emp_user_id, but this is not the primary key in hr_emp_ids. This is why the associated data isn't being returned. You need to set up something like this:

public $belongsTo = array(
    'HrEmpId' => array(
        'foreignKey' => false,
         'conditions' => array(
             'HrEmpId.emp_user_id = HrCase.emp_user_id'
         ),
    )
);

see this for more info How to associate model in CakePHP by fields not named by convention?

CakePHP: Joined Table Data Not Displaying in View

1 Answers