Unable to `contain()` same table multiple times using different aliases

[mehov]

Description

Say I have a join table where I map products to products, for example two counterparts of each other. (Please assume it makes sense.)

Then I want to select a pair of products out of that table. Each product belongsTo category, and finally each category belongsTo store.

In the join table where the product pairs are, I can define the association twice:

    $this->belongsTo('Products', [
        'foreignKey' => 'product_id',
        'joinType' => 'INNER',
    ]);
    $this->belongsTo('RefProducts', [
        'className' => 'Products',
        'foreignKey' => 'ref_product_id',
        'joinType' => 'INNER',
    ]);

But when I find() a product, attempting to contain its counterpart and its own containments, I don't get both sets of contained information. I'm assuming this is because the contained joins use the same aliases. I tried to overwrite the aliases for each individual contain, but it didn't work. My first attempt was the most obvious:

->contain([
    'Categories' => [
        'Stores' => [
        ]
    ],
    'ProductsProducts' => [
        'RefProducts' => [
            'Categories' => [
                'alias' => 'RefCategories',
                'Stores' => [
                    'className' => 'RefStores',
                ]
            ],
        ],
    ],
])

That resulted in The alias association is not defined. I tried a few more and then finally found a complete list of all accepted options:

https://github.com/cakephp/cakephp/blob/27ef3b06e6a9ae68dbce20e9ed76f8b27a0ed5c4/src/ORM/EagerLoader.php#L53-L65

Unless I'm missing something, there doesn't seem to be a way to manually set an alias for an individual contain?

CakePHP Version

4.4.14

PHP Version

8.3.1

[bgrinter]

Also experiencing this problem which I've mentioned in Discourse https://discourse.cakephp.org/t/association-problem-tables-with-the-same-name/12344/5

Can provide more info if needed

[dereuromark]

Even though RefProducts is a different association, it still points to the Products table which has a Categories association. The conflict happens because:

1. The Products table instance used by Products association has Categories
2. The Products table instance used by RefProducts association ALSO has Categories
3. These are the same association name on what CakePHP treats as the same table instance

The real fix requires creating different association names at EACH level:

  // In Products table (when accessed as RefProducts)
  $this->belongsTo('RefCategories', [
      'className' => 'Categories',
      'foreignKey' => 'category_id',
  ]);

  // In Categories table (when accessed as RefCategories) 
  $this->belongsTo('RefStores', [
      'className' => 'Stores',
      'foreignKey' => 'store_id',
  ]);

This is indeed a limitation - CakePHP doesn't allow the same table to have different association configurations based on how it's accessed. Each table class has one set of associations that are shared across all references to it.

[dereuromark]

@markstory Is there anything we want to do here? Or do we close this with the possible solution via more creative aliasing?

[markstory]

Is there anything we want to do here?

I don't have any immediate actions on this. I agree it is unexpected behavior that should be corrected. But I'm also not impacted, and it based on the activity on this it isn't a burning issue for others.

[dereuromark]

https://github.com/cakephp/cakephp/pull/18932 open, requires a bit more work though if anyone has some ideas/time.

[mehov]

#18932 open, requires a bit more work though if anyone has some ideas/time.

Thank you for looking into this! It's been a while, @nicosp you said in #18929 you've been working on code that deals with this recently, maybe you could provide some feedback if you have any?

[nicosp]

#18932 open, requires a bit more work though if anyone has some ideas/time.

Thank you for looking into this! It's been a while, @nicosp you said in #18929 you've been working on code that deals with this recently, maybe you could provide some feedback if you have any?

We are affected by this but haven't found a way to resolve it. So far we workaround the issue by using unique names for all associations.

Another approach we use, is to use joins and an alias service that simply returns unique names through an autoincrement integer.

Neither solves the issue but it allows us to workaround specific cases

[othercorey]

@mehov Can you look at the new test and tell me if there's something missing.

[dereuromark]

@markstory Do we want to tackle anything here, or closing it as works-for-me?