Association Problem - Tables with the same name

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!

Same issue as Unable to `contain()` same table multiple times using different aliases · Issue #17679 · cakephp/cakephp · GitHub

I don’t have a solution for you, but maybe I can find some time after christmas to look at that.