Association Problem - Tables with the same name

[bgrinter]

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!

[KevinPfeifer]

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.

[bgrinter]

cool - hoping I don’t need to do too much rework and haven’t painted myself into a corner here

[Rods]

did you try the matching?? Query Builder - 5.x

[bgrinter]

It’s related to the issue Kevin mentions above.

I’ve created a kludge workaround with a virtual field in the member_statuses table, but I’d like something better.

[KevinPfeifer]

Am I correct, that you have this DB structure currently (simplified)?

[bgrinter]

Hi Kevin

Structure is like this (simplified)

In the Members model

        $this->belongsTo('CurrentMemberStatuses', [
            'className' => 'MemberStatuses',
            'foreignKey' => 'current_member_status_id'
        ]);
        $this->belongsTo('PreviousMemberStatuses', [
            'className' => 'MemberStatuses',
            'foreignKey' => 'previous_member_status_id'
        ]);
        $this->hasMany('MemberStatuses', [
            'dependent' => true,
            'foreignKey' => 'member_id'
        ]);

So a member has potentially many statuses, although I’m interested in the Current Status, and Previous Status

Every time I save a Member Status - I update the current and previous member status

[KevinPfeifer]

I can’t reproduce your problem.

Look at this:

This is a simple

        $query = $this->Members->find()
            ->contain(['CurrentMemberStatuses', 'PreviousMemberStatuses']);
        $members = $this->paginate($query);

with the associations defined as you have them above.

The generated SQL query is using the aliases as defined in the association names.

I am on CakePHP 5.1.4

[KevinPfeifer]

And even if I generall fetch the connected MemberStatuses as well via

        $query = $this->Members->find()
            ->contain([
                'MemberStatuses',
                'CurrentMemberStatuses',
                'PreviousMemberStatuses'
            ]);
        $members = $this->paginate($query);

it works fine, because it fetches those a separate query as you can see here

Are you 100% certain you have the correct association names present in your contain config array?

[bgrinter]

Sorry I’ve not replied sooner - I was away last week with limited access.

I’ll check the above and come back to you