CakePHP 3 - Two foreign keys linked to the same table - wrong foreign key being used

Question

I have two tables, each with their own attributes and with two foreign keys linking them:

Sessions

id
staff_id (primary staff member) - foreign key linking to Staff table
assistant_id (secondary staff member) - foreign key linking to Staff table
date

Staff

id
user_id (foreign key linking to Users table)
name

When I attempt to do a Find query, to display a Session with its primary staff member, it shows the assistant staff member instead in Index.ctp.

For example, if Session with ID=1 has staff_id=3 and assistant_id=null, the Staff column in Index shows up as blank. If however, assistant_id=1, it shows the staff with ID=1 (the assistant staff) instead of ID=3 (the primary staff).

In my SessionsTable, I have the following:

$this->belongsTo('Staff', [
    'foreignKey' => 'staff_id'
]);
$this->belongsTo('Staff', [
    'foreignKey' => 'assistant_id'
]);

In my StaffTable, I have the following:

$this->hasMany('Sessions', [
    'foreignKey' => 'staff_id'
]);
$this->hasMany('Sessions', [
    'foreignKey' => 'assistant_id'
]);

In my SessionsController, the index function has the following find request assigned to a Cake variable:

$sessions = $this->Sessions->find('all', [
    'contain' => ['Staff']
    ]);

In the Index's ctp page, the table is as follows:

<table class="bookingsTables display" id="confirmedTable">
    <thead>
        <tr>
            <th scope="col"><?= $this->Paginator->sort('id', 'ID') ?></th>
            <th scope="col"><?= $this->Paginator->sort('staff_id', 'Primary Staff Member') ?></th>
            <th scope="col"><?= $this->Paginator->sort('date', 'Date') ?></th>
            <th scope="col" class="actions"><?= __('Actions') ?></th>
        </tr>
    </thead>
    <tbody>
    <?php foreach ($sessions as $session): ?>
        <tr>
            <td><?= h($session->id) ?></td>
            <td><?= $session->has('staff') ? h($session->staff->name) : '' ?></td>
            <td><?= h($session->date) ?></td>
            <td class="actions">
                <?= $this->Html->link(__('View'), ['action' => 'view', $session->id]) ?>

            </td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>

Also with regards to the Find query, if I log in as a staff member and do a find of all Sessions where they are the primary staff member, it will not be able to find any.

$id = $this->Auth->user('id'); //gets the id of the logged in user
$sessions = $this->Sessions->find('all', [ 
        'contain' => ['Staff'],
        'conditions' => ['user_id' => $id]
        ]); //find Sessions where staff_id = staff member who's logged in

Or alternatively:

$id = $this->Auth->user('id'); //gets the id of the logged in user
$sessions = $this->Sessions->find('all', [
    'contain' => ['Staff', 'Staff.Users'],
    'conditions' => ['Users.id' => $id] 
]); //find Sessions where staff_id = staff member who's logged in

Possible duplicate of [Linking same table with two foreign keys in cakephp 3](https://stackoverflow.com/questions/39086686/linking-same-table-with-two-foreign-keys-in-cakephp-3) — monsur.hoq, Apr 29 '19 at 05:33

Matteo Kovacic · Accepted Answer · 2017-03-25T19:03:49.367

2

Cake will override first instance of Staff if second one exist. To avoid this your model should be like:

$this->belongsTo('Staff', [ 
    'className' => 'Staff', 
    'foreignKey' => 'staff_id', 
    'propertyName' => 'staff'
]);

$this->belongsTo('Assistant', [ 
   'className' => 'Staff', 
   'foreignKey' => 'assistant_id', 
   'propertyName' => 'assistant'
]);

And then in your view replace

<td><?= $session->has('staff') ? h($session->staff->name) : '' ?></td>

with:

<td><?= $session->has('staff') ? h($session->staff->name) : $session->has('assistant') ? h($session->assistant->name) : '' ?></td>

edited Mar 25 '17 at 19:03

answered Mar 24 '17 at 11:52

Matteo Kovacic

159
3

That won't work, the problem is that aliases must be unique, you cannot use `Staff` for both associations, one should be named `Assistant`, this will also automatically ensure unique property names. Similarily the associations in `StaffTable` need to be renamed. – ndm Mar 24 '17 at 12:47
Thanks ndm, edited my answer to correct unique name. – Matteo Kovacic Mar 24 '17 at 13:35
That works for showing the Assistant, but not the main Staff. In Cake variables, Session would contain Assistant instead of Staff. I would like to achieve the opposite. – mistaq Mar 24 '17 at 15:31
Sorry, that comment was made before you updated your answer. Now I'm getting this error: `Notice (8): Trying to get property of non-object.` This is even if the value for staff_id is not null. – mistaq Mar 24 '17 at 15:36
In contain you need to add Assistant – Matteo Kovacic Mar 24 '17 at 16:06
Okay, it's fixed. By the way, your replacement of the table row in the View can be shortened: `= $session->has('staff') ? h($session->staff->name): '' ?>` is sufficient for staff_id, while `= $session->has('assistant') ? h($session->assistant->name) : '' ?>` can be used for assistant_id – mistaq Mar 25 '17 at 00:33

CakePHP 3 - Two foreign keys linked to the same table - wrong foreign key being used

1 Answers1