join() comes before contain(), results in "Column not found"

[mehov]

Description

What I have

return $this
    ->find('all')
    ->contain([
        'Categories' => [
            'Stores' => [
            ]
        ],
        'ProductsProducts' => [
            'RefProducts' => [
            ],
        ],
    ])
    ->join([
        [
            'alias' => 'RefCategories',
            'table' => 'Categories',
            'type' => 'LEFT',
            'conditions' => 'RefProducts.category_id = RefCategories.id'
        ],
    ])
;

What happens

In the query that CakePHP generates for that find, my manual join I defined last actually comes before the "contained" tables.

SELECT 
  *
FROM 
  Products Products 
  LEFT JOIN Categories RefCategories ON RefProducts.category_id = RefCategories.id 
  LEFT JOIN Categories Categories ON Categories.id = Products.category_id 
  INNER JOIN Stores Stores ON Stores.id = Categories.store_id 
  LEFT JOIN Products_Products ProductsProducts ON ProductsProducts.id = Products.id 
  INNER JOIN Products RefProducts ON RefProducts.id = ProductsProducts.ref_product_id 

As a result,

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'RefProducts.category_id' in 'on clause'

What I expected to happen

Because I defined the join() after contain(), I expected the contained tables to be available before the join kicks in.

If I copy the SQL from DebugKit and manually edit it to where the contained tables are joined first, the query works when I run it in Workbench

CakePHP Version

4.4.14

PHP Version

8.3.1

[markstory]

This is likely caused by join() clauses being separate from contain() clauses. The reason for this separation is that not all contain() operations generate joins. #17184 is somewhat related.

[mehov]

@markstory but shouldn't it generate a bunch of left joins because it's a chain of belongsTo associations?

One can normally get Product to Category to Store all in one query

[markstory]

but shouldn't it generate a bunch of left joins because it's a chain of belongsTo associations?

Sure, in your situation that is true, but that isn't always the case as contain() can be used for all association types.