I have a problem with tables, similar to the problem here
I have a table of Members of an organisation, and a member has a Status Type that can change over time. This is tracked with a HasMany relationship
$this->hasMany('MemberStatuses', [
'dependent' => true,
'foreignKey' => 'member_id'
]);
I can then see a chronological history of their membership status, the most recent entry by date is considered the current status of that member. I often need to look up the current status, and the previous status to this, especially with ex-members where I might want to see their status before leaving - e.g. Ex-Member as of 31/7/2024, prior to this an Associate member.
To achieve this I have 2 BelongsTo relationships.
$this->belongsTo('CurrentMemberStatuses', [
'className' => 'MemberStatuses',
'foreignKey' => 'current_member_status_id'
]);
$this->belongsTo('PreviousMemberStatuses', [
'className' => 'MemberStatuses',
'foreignKey' => 'previous_member_status_id'
]);
Everything works fine, except is seems when I go to search using the FriendOfCake Search plugin.
This works fine, and I can search on MemberStatusTypes.name with no problem
$query = $this->Members
->find('search', ['search' => $this->request->getQuery()])
->contain([
'CurrentMemberStatuses.MemberStatusTypes'
]
)
Looking in DebugKit I see this join in the SQL log
LEFT JOIN `member_statuses` `CurrentMemberStatuses` ON `CurrentMemberStatuses`.`id` = `Members`.`current_member_status_id`
INNER JOIN `member_status_types` `MemberStatusTypes` ON `MemberStatusTypes`.`id` = `CurrentMemberStatuses`.`member_status_type_id`
However, when I query the Current and Previous Statuses, the MemberStatusTypes table isn’t returned in the query, but in a sub query
$query = $this->Members
->find('search', ['search' => $this->request->getQuery()])
->contain([
'CurrentMemberStatuses.MemberStatusTypes',
'PreviousMemberStatuses.MemberStatusTypes'
])
I see this in the SQL log
LEFT JOIN `member_statuses` `CurrentMemberStatuses` ON `CurrentMemberStatuses`.`id` = `Members`.`current_member_status_id`
LEFT JOIN `member_statuses` `PreviousMemberStatuses` ON `PreviousMemberStatuses`.`id` = `Members`.`previous_member_status_id`
The problem is CurrentMemberStatuses and PreviousMemberStatuses both have “MemberStatusTypes”. It’s a fairly obscure bug only causing issue when I try to search on queries containing both BelongTo relationships. What options do I have resolve? Can I apply aliases to the names?
Thanks in advance for any help!