如何在drogon的model.json中正确声明复合键数据库关系？

场景说明 #

QuizQuestions表以quiz_ID和question_ID作为复合主键，Responses表通过这两个字段与QuizQuestions表建立关联，对应的SQL建表语句如下：

CREATE TABLE `QuizQuestions` (
  `quiz_ID` int(11) NOT NULL,
  `question_ID` int(11) NOT NULL,
  `ordinal` int(11) NOT NULL,
  PRIMARY KEY (`quiz_ID`,`question_ID`),
  KEY `QuizQuestions_Questions_FK` (`question_ID`),
  CONSTRAINT `QuizQuestions_Questions_FK` FOREIGN KEY (`question_ID`) REFERENCES `Questions` (`question_ID`),
  CONSTRAINT `QuizQuestions_Quizzes_FK` FOREIGN KEY (`quiz_ID`) REFERENCES `Quizzes` (`quiz_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `Responses` (
  `student_ID` int(11) NOT NULL,
  `quiz_ID` int(11) NOT NULL,
  `attempt_no` int(11) NOT NULL,
  `question_ID` int(11) NOT NULL,
  `student_attempt` varchar(100) DEFAULT NULL,
  `teacher_feedback` varchar(100) DEFAULT NULL,
  `student_feedback` varchar(100) DEFAULT NULL,
  `mark` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_ID`,`quiz_ID`,`attempt_no`,`question_ID`),
  KEY `Responses_QuizQuestions_FK` (`quiz_ID`,`question_ID`),
  CONSTRAINT `Responses_QuizQuestions_FK` FOREIGN KEY (`quiz_ID`, `question_ID`) REFERENCES `QuizQuestions` (`quiz_ID`, `question_ID`),
  CONSTRAINT `Responses_Students_FK` FOREIGN KEY (`student_ID`) REFERENCES `Students` (`student_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

错误配置的问题 #

如果像下面这样分开声明两个单键的has many关系，会导致drogon_ctl create model生成重复的关系函数，触发源码重定义错误，同时逻辑上也不正确：

{
    "type": "has many",
    "original_table_name": "QuizQuestions",
    "original_table_alias": "",
    "original_key": "quiz_ID",
    "target_table_name": "Responses",
    "target_table_alias": "",
    "target_key": "quiz_ID",
    "enable_reverse": false
},
{
    "type": "has many",
    "original_table_name": "QuizQuestions",
    "original_table_alias": "",
    "original_key": "question_ID",
    "target_table_name": "Responses",
    "target_table_alias": "",
    "target_key": "question_ID",
    "enable_reverse": false
},

正确的复合键关联配置 #

要正确声明复合键的has many关系，需要将复合键字段以数组形式填入original_key和target_key中，只保留一个关系条目即可：

{
    "type": "has many",
    "original_table_name": "QuizQuestions",
    "original_table_alias": "",
    "original_key": ["quiz_ID", "question_ID"],
    "target_table_name": "Responses",
    "target_table_alias": "",
    "target_key": ["quiz_ID", "question_ID"],
    "enable_reverse": false
}

这样配置后，drogon_ctl会生成正确的关联函数，既不会出现重定义错误，也能准确映射复合键的关联逻辑。

内容的提问来源于stack exchange，提问作者ArchNemSyS