SQL error when loading multiple associations with the same name

Need Help
1
  • Users hasMany Revisions
  • Revisions belongsTo Posts
  • Posts hasOne Revisions where Revisions.is_latest = 1

I want to load the latest Revision for each Revision that the User has:

TableRegistry::get('Users')->loadInto($user, ['Revisions.Posts.Revisions']);

However, I get an SQL error about ambiguous table/alias Revision. If I load it in two steps, it works fine. I could change the name of the second Revisions association to LatestRevisions but that wouldn’t be correct in all cases (e.g. when I actually do want all the revisions). I was wondering whether this might call for a bug report?

2

Revisions belongsTo Posts is joinable, and Posts hasOne Revisions is joinable too, so both will end up in the same query, and that will not work as you cannot use the same alias more than once.

You can either create an additional association (no need to replace), like LastRevisions as you mentioned, or you can change the association’s strategy to select, which will cause the records to be fetched in a separate query instead of via a join. This can be done on association level via the strategy option/setter:

$this->hasOne('Revision', [
    // ...
    'strategy' => \Cake\ORM\Association::STRATEGY_SELECT,
]);

or on the fly in the contain config for Revisions, so that it only applies to this specific query:

[
    'Revisions.Posts.Revisions' => [
        'strategy' => \Cake\ORM\Association::STRATEGY_SELECT
    ]
]
3

Another solution is changing the name of the "Posts hasOne Revisions" Asociation, like

$this->hasOne('LatestRevision', [
    // ...
    'className' => 'Revisions',
]);

TableRegistry::get('Users')->loadInto($user, ['Revisions.Posts.LatestRevision']);