Two foreing key to same table wrong join on second key about cfwheels HOT 6 OPEN

Reported by @blastiko on issue #1216. Most important was his discovery when using a where statement:

Update
This NOT work:
moduleclassroom=model("moduleclassroom").findByKey(select="teachers.firstname,secondteachers.firstname", key=params.key, include="teacher,secondteacher");

This NOT work:
moduleclassroom=model("moduleclassroom").findByKey(select="teachers.firstname,secondteachers.firstname AS secondteachers_firstname", key=params.key, include="teacher,secondteacher");

This NOT work:
moduleclassroom=model("moduleclassroom").findAll(select="teachers.firstname,secondteachers.firstname", where="moduleclassrooms.id = #params.key#", include="teacher,secondteacher");

This WORK:
moduleclassroom=model("moduleclassroom").findAll(select="teachers.firstname,secondteachers.firstname AS secondteachers_firstname", where="moduleclassrooms.id = #params.key#", include="teacher,secondteacher");

from cfwheels.

There appears to be an overwriting of table "teacher" data relative to the "secondteacher" data.
Perhaps is a alias building error?
Coused by wrong use (or not present) of AS clause in the query?

from cfwheels.

We made need an "alias" parameter on belongTo if the user wishes to join a table twice. This is a little more of @blastiko 's troubleshooting. I believe our auto-adding the table name to the select statement may be causing the issues.

@dbelanger: What happens if

1. you only include the secondTeacher and
   

2. you reverse the include order (ie. include= "secondTeacher, teacher")
   

@blastiko: With modelName="teachers" in module cfc

I get this error: "Error Executing Database Query. --Unknown column 'teachers.id' in 'field list'"
I get this error: "Executing Database Query. --Unknown column 'teachers.deletedat' in 'on clause'"

With modelName="teacher" in module cfc

I get this error: "Error Executing Database Query. --Unknown column 'teachers.firstname' in 'field list'"
I get this error: "Error Executing Database Query. --Unknown column 'teachers.deletedat' in 'on clause'"

If i use "teacher,secondteacher" i get no error but same initial problem

I've got those errors indipendently if i use modelName="teachers" or modelName="teacher", but only if i use include="secondtheacher,teacher". If i use include="teacher,secondtheacher" i get no errors, only wrong data for secondteacher.

from cfwheels.

I've definitely run into similar issues over the years and ended up just writing SQL. Glad this is being reviewed! Thanks for all the hard work.

from cfwheels.

I was trying a similar model and i noticed, that the both tables are used in the query like ...

FROM table
LEFT OUTER JOIN tablejoin ON table.first_key = tablejoin.primarykey
LEFT OUTER JOIN tablejoin AS tablejoin_ies ON table.second_key = tablejoin_ies.primarykey

So, there is a attached _ies in the join and I cannot find this in the wheels repository or somewhere else. Where in the code is this attached?

from cfwheels.

FYI: There is a function $singularizeOrPluralize() in wheels/global/internal.cfm, at line 1080 ff.

from cfwheels.