Two foreing key to same table wrong join on second key

[blastiko]

Hi guys

Table "moduleclassrooms" has two foreing key (teacherid and secondteacherid) to "teachers" table:
CREATE TABLE moduleclassrooms (
id INT(11) NOT NULL AUTO_INCREMENT,
moduleid INT(11) NOT NULL,
classroomid INT(11) NULL DEFAULT NULL,
teacherid INT(11) NULL DEFAULT NULL,
secondteacherid INT(11) NULL DEFAULT NULL,
masterlocationid INT(11) NOT NULL,
streaming TINYINT(1) UNSIGNED NULL DEFAULT NULL,
videolink VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
streamingcode VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
assignmentlettersend TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
assignmentlettersign TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
feedbackteacher TEXT NULL DEFAULT NULL COLLATE 'utf8_general_ci',
createdat DATETIME NULL DEFAULT NULL,
updatedat DATETIME NULL DEFAULT NULL,
deletedat DATETIME NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX fk_modulesclassrooms_modules1_idx (moduleid) USING BTREE,
INDEX fk_modulesclassrooms_classrooms1_idx (classroomid) USING BTREE,
INDEX fk_modulesclassrooms_masterslocations1_idx (masterlocationid) USING BTREE,
INDEX streaming_idx (streaming) USING BTREE,
INDEX fk_modulesclassrooms_teachers1_idx (teacherid) USING BTREE,
INDEX fk_moduleclassrooms_secondteacherid1 (secondteacherid) USING BTREE,
CONSTRAINT fk_moduleclassrooms_secondteacherid1 FOREIGN KEY (secondteacherid) REFERENCES teachers (id) ON UPDATE CASCADE ON DELETE SET NULL,
CONSTRAINT fk_modulesclassrooms_classrooms1 FOREIGN KEY (classroomid) REFERENCES classrooms (id) ON UPDATE CASCADE ON DELETE SET NULL,
CONSTRAINT fk_modulesclassrooms_masterslocations1 FOREIGN KEY (masterlocationid) REFERENCES masterlocations (id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_modulesclassrooms_modules1 FOREIGN KEY (moduleid) REFERENCES modules (id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_modulesclassrooms_teachers1 FOREIGN KEY (teacherid) REFERENCES teachers (id) ON UPDATE CASCADE ON DELETE SET NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=28
;

This is "teachers" table:
CREATE TABLE teachers (
id INT(11) NOT NULL AUTO_INCREMENT,
firstname VARCHAR(250) NOT NULL COLLATE 'utf8_general_ci',
lastname VARCHAR(250) NOT NULL COLLATE 'utf8_general_ci',
cv VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
photo VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
description VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
address VARCHAR(250) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
company VARCHAR(250) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
companyrole VARCHAR(250) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
email VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
phone VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
password VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
borndate DATE NULL DEFAULT NULL,
borncity VARCHAR(250) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
vatfc VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
ibancode VARCHAR(45) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
roleid INT(11) NULL DEFAULT NULL,
createdat DATETIME NULL DEFAULT NULL,
updatedat DATETIME NULL DEFAULT NULL,
deletedat DATETIME NULL DEFAULT NULL,
master1 VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
master2 VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
modulo1 VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
modulo2 VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
modulo3 VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
modulo4 VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
PRIMARY KEY (id) USING BTREE,
INDEX email_idx (email) USING BTREE,
INDEX fk_teachers_roles0 (roleid) USING BTREE,
CONSTRAINT fk_teachers_roles0 FOREIGN KEY (roleid) REFERENCES roles (id) ON UPDATE CASCADE ON DELETE SET NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=387
;

This is "Moduleclassroom.cfc" model:
component extends="Model" {
function config() {
belongsTo(name = "module");
belongsTo(name = "classroom", joinType="OUTER");
belongsTo(name = "teacher", joinType="OUTER");
belongsTo(name = "secondteacher", modelName="teacher", foreignKey="secondteacherid", joinType="OUTER");
belongsTo(name = "masterlocation");
hasMany(name = "attendees");
hasMany(name = "questions");
hasMany(name = "moduledocuments");
hasMany(name = "moduleclassroomperiods");
nestedProperties(associations="moduleclassroomperiods", allowDelete=true);
}
}

In controller:
moduleclassroom=model("moduleclassroom").findByKey(key=params.key, include="teacher,secondteacher");

This is dump of "moduleclassroom":

As you can see the datas for "secondteacher" is not refer to id 109 but always to "teacher" of id 77

It is a bug or i wrong anything?

[dbelanger]

Which version of CFWheels and which database (and version) are you using?

[dbelanger]

So it's not a pluralization bug, good. 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

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

With modelName="teacher" in module cfc

1. I get this error: "Error Executing Database Query. --Unknown column 'teachers.firstname' in 'field list'"
2. 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

[dbelanger]

Sorry, but when your changed modelName back to teacher (like it was before), you shouldn't have received any errors. Are you sure the framework is reloading correctly? Did you add a password necessary to reload the framework?

[blastiko]

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.

[blastiko]

I have set(environment = "development"); in config/environment.cfm and no password set for reload=true

[blastiko]

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");

[dbelanger]

This is enough to promote this to an issue @blastiko. I've gone ahead and done that (#1217). This looks to have something to do with referring to the same table twice.

[blastiko]

Thank you dbelanger for your support.

[zainforbjs]

@dbelanger The issues has been fixed, please have your review and LMK your feedback. Thanks