Unable to load association. Ensure foreign key is selected

Need Help
1

Upgrading an app from Cake 3.8 to 4.2. Getting errors on code like this:

$query = $this->People->find();
$count = $query
	->select(['status', 'person_count' => $query->func()->count('People.id')])
	->select($this->People->Affiliates)
	->matching('Affiliates', function (Query $q) use ($affiliates) {
		return $q->where(['Affiliates.id IN' => $affiliates]);
	})
	->group(['AffiliatesPeople.affiliate_id', 'People.status'])
	->order(['Affiliates.name', 'People.status'])
	->toArray();

This worked in Cake 3.8, but in 4.2 it’s saying Unable to load 'Affiliates' association. Ensure foreign key in 'People' is selected. People is a belongsToMany relation to Affiliates. If I get rid of everything about Affiliates in this query, it works, but of course doesn’t return the results I need.

The error comes from EagerLoader::loadExternal, and the comment there says “Decorates the passed statement object in order to inject data from associations that cannot be joined directly.” But in this use case, the matching call means that it is joined directly, and it seems maybe that’s what’s breaking it? It’s trying to inject results from a second query, but there was no second query. I tried adding ->contain('Affiliates') just for kicks, but it didn’t change anything.

Note that the query works just fine. It’s logged if I turn on logging, it looks like it expect it to (a single query), and when I execute it directly in MySQL, it returns correct results. It’s only Cake’s parsing of those results into entities that is faulty.

I don’t see any updates that would obviously fix this in the latest 4.x version of that function, but maybe it’s resolved elsewhere in the call stack? Could it be Simplify generating nested contains in setMatching() · cakephp/cakephp@ba81c49 · GitHub ? Doesn’t seem like it… Anyone recognize this as a resolved issue, or otherwise have ideas on how to work around it?

2

I personally would try to replace

->select($this->People->Affiliates)

with

->enableAutoFields()

since calling ->select() will disable all other fields to be automatically selected/converted.

3

Exact same error with this change.

4

Tried upgrading Cake to version 4.3.11 and 4.5.4, and the same error occurs in both. So at least I know more upgrades aren’t the immediate solution.

5

If I simply comment out throw new InvalidArgumentException($message); in EagerLoader::loadExternal, then it works fine, I get the results I’m expecting, in the format I’m expecting, everything renders correctly, etc. I’m assuming it’s there for a good reason, though…

6

Changing the association between these tables, from the default “select” strategy to “subquery”, at least in this particular controller action, changes how the EagerLoader expects results to arrive, in such a way that it skips this check. Still feels like a bit of a bug in the core, but at least I can move along now.

$this->People->Affiliates->setStrategy('subquery');

7

Well, I didn’t get to move along very far. :frowning: A later query in the same action groups by a calculation, but that calculated field name is now referenced by the subquery, and it doesn’t exist there. Back to the drawing board. Will try to put together a proper bug report for this at some point.

$query = $this->People->find();
$age_count = $query
	->select([
		'age_bucket' => $query->func()->floor(['(YEAR(NOW()) - YEAR(birthdate)) / 5' => 'identifier']),
		'person_count' => $query->func()->count('People.id'),
	])
	->select($this->People->Affiliates)
	->matching('Affiliates', function (Query $q) use ($affiliates) {
		return $q->where(['Affiliates.id IN' => $affiliates]);
	})
	->where([
		'People.status' => 'active',
		'People.birthdate IS NOT' => null,
	])
	->group(['AffiliatesPeople.affiliate_id', 'age_bucket'])
	->order(['Affiliates.name', 'age_bucket'])
	->toArray();
8

The only other thing I can think of to help you here is to use the database methods instead of the ORM methods - aka manually joining your tables instead of using the ORM methods.

E.g.

$innerQuery = $this->setAlias('inner_query')
    ->find()
    ->leftJoin('ftp_domain_categories', 'ftp_domain_categories.category_id = outer_query.id')
    ->leftJoin('ftp_domains', 'ftp_domain_categories.ftp_domain_id = ftp_domains.id')
    ->select([
        'outer_query.id',
        'child_category_name' => 'outer_query.name',
        'outer_query.parent_id',
        'count' => $query->func()
            ->count('ftp_domains.id'),
    ])
    ->groupBy(['outer_query.parent_id', 'outer_query.name',])
    ->having(['outer_query.parent_id IS NOT' => null]);

return $this->setAlias('outer_query')
    ->find()
    ->select([
        'term_id' => 'inner_query.outer_query__id',
        'child_term_name' => 'inner_query.child_category_name',
        'parent_category_name' => 'outer_query.name',
        'count' => 'inner_query.count',
        // maybe other cols here
    ])
    ->innerJoin(['inner_query' => $innerQuery], 'inner_query.outer_query__parent_id = outer_query.id');

This is just an example of mine where I didn’t find exactly what I needed from the ORM methods but had an exact query I wanted to replicate.

$this is just the table instance since this is part of a finder method of mine.

9

Finally got back to this today, and the issue is not in the initial query, but when it tries to load translations for one of the tables. I’m currently using the EAV strategy, but will be changing to the shadow table as part of this overall upgrade, as I have a number of issues related to EAV (things not sorting according to translated fields, for example), so I’ve worked around the issue for now by removing the translate behaviour from the table before running this particular query, and will come back to it once I get shadow table implemented.

10

this query combined with translations seems like a major pain in the ass to be honest :sweat: