When using a nested include (e.g. ParentModel(ChildModel)), Wheels 3+ generates a nested join that MySQL rejects with Unknown column 'table.column' in 'on clause'.
To Reproduce
Given models with associations:
Relationship belongsTo Contact (as SecondaryContact, foreignKey: relatedcontactid)
Contact belongsTo User
Note: there are Contact and SecondaryContact models which both use table("contacts")
Calling:
model("Relationship").findAll(
include = "RelationshipType,SecondaryContact(User)",
where = "contactid = 1"
)
Generates:
FROM relationships
LEFT OUTER JOIN user
INNER JOIN relationshiptypes ON relationships.relationshiptypeid = relationshiptypes.id
INNER JOIN contacts ON relationships.relatedcontactid = contacts.contactid
ON contacts.userid = users.id
MySQL error: Unknown column 'relationships.relatedcontactid' in 'on clause'
Expected behavior
Wheels should generate flat JOINs so all tables in the FROM clause remain in scope for every ON
condition:
FROM relationships
INNER JOIN relationshiptypes ON relationships.relationshiptypeid = relationshiptypes.id
INNER JOIN contacts ON relationships.relatedcontactid = contacts.contactid
LEFT JOIN users ON contacts.userid = users.id
Additional context
This is a MySQL scoping rule: when INNER JOINs appear without parentheses after a LEFT OUTER JOIN, they
bind to it and form a nested join expression. Inside that expression only the tables named within it
are in scope — the root FROM table (relationships) is invisible to those ON conditions. Flat LEFT JOINs
at the same level keep every table in scope.
This worked in Wheels 2, which generated flat joins for multi-level includes. The regression appears to
be in how Wheels 3's SQL generator handles Parent(Child) nested includes.
When using a nested include (e.g. ParentModel(ChildModel)), Wheels 3+ generates a nested join that MySQL rejects with Unknown column 'table.column' in 'on clause'.
To Reproduce
Given models with associations:
Relationship belongsTo Contact (as SecondaryContact, foreignKey: relatedcontactid)
Contact belongsTo User
Note: there are Contact and SecondaryContact models which both use
table("contacts")Calling:
model("Relationship").findAll(
include = "RelationshipType,SecondaryContact(User)",
where = "contactid = 1"
)
Generates:
Expected behavior
Wheels should generate flat JOINs so all tables in the FROM clause remain in scope for every ON
condition:
Additional context
This is a MySQL scoping rule: when INNER JOINs appear without parentheses after a LEFT OUTER JOIN, they
bind to it and form a nested join expression. Inside that expression only the tables named within it
are in scope — the root FROM table (relationships) is invisible to those ON conditions. Flat LEFT JOINs
at the same level keep every table in scope.
This worked in Wheels 2, which generated flat joins for multi-level includes. The regression appears to
be in how Wheels 3's SQL generator handles Parent(Child) nested includes.