sequelize 外键关联_Sequelize.js一对多关系的外键

Sequelize.js一对多关系的外键

我正在使用Node.js / Express和MySQL与Sequelize.js ORM创build一个调查应用程序。

我无法正确设置2个模型之间的关系。 我想问题的答案表中的外键。

// define the Questions table var Questions = sequelize.define('Questions', { qId: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true}, question: Sequelize.STRING }, { timestamps: false }); // define the Answers table var Answers = sequelize.define('Answers', { aId: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true}, answer: Sequelize.STRING, answer_count: { type: Sequelize.INTEGER, defaultValue: 0} }, { timestamps: false }); // define one-to-many relationship Questions.hasMany(Answers, {as: 'Answers', foreignKey: 'qId'}); Questions.sync({force: true}).then(function() { // OPTIONAL: create a new question upon instantiating the db using sequelize Questions.create({question: 'what is your language?'}); Questions.create({question: 'what is your drink?'}); console.log('created Questions table'); }).catch(function(error) { console.log('error creating Questions table'); }); Answers.sync({force: true}).then(function() { Answers.create({answer: 'python', qId: 1}); Answers.create({answer: 'javascript', qId: 1}); Answers.create({answer: 'ruby', qId: 1}); Answers.create({answer: 'c++', qId: 1}); Answers.create({answer: 'manhattan', qId: 2}); Answers.create({answer: 'cosmopolitan', qId: 2}); console.log('created Answers table'); }).catch(function(error) { console.log('error creating Answers table'); });

但是当我做MySQL查询时：

select * from Questions, Answers where Answers.qId=2;

它显示了以下内容：

mysql> select * from Answers; +-----+--------------+--------------+------+ | aId | answer | answer_count | qId | +-----+--------------+--------------+------+ | 1 | python | 0 | 1 | | 2 | javascript | 0 | 1 | | 3 | ruby | 0 | 1 | | 4 | c++ | 0 | 1 | | 5 | manhattan | 0 | 2 | | 6 | cosmopolitan | 0 | 2 | +-----+--------------+--------------+------+ 6 rows in set (0.00 sec) mysql> select * from Questions; +-----+------------------------+ | qId | question | +-----+------------------------+ | 1 | what is your language? | | 2 | what is your drink? | +-----+------------------------+ 2 rows in set (0.00 sec) mysql> select * from Questions, Answers where Answers.qId=2; +-----+------------------------+-----+--------------+--------------+------+ | qId | question | aId | answer | answer_count | qId | +-----+------------------------+-----+--------------+--------------+------+ | 1 | what is your language? | 5 | manhattan | 0 | 2 | | 1 | what is your language? | 6 | cosmopolitan | 0 | 2 | | 2 | what is your drink? | 5 | manhattan | 0 | 2 | | 2 | what is your drink? | 6 | cosmopolitan | 0 | 2 | +-----+------------------------+-----+--------------+--------------+------+

当我想要显示

mysql> select * from Questions, Answers where Answers.qId=2; +-----+------------------------+-----+--------------+--------------+------+ | qId | question | aId | answer | answer_count | qId | +-----+------------------------+-----+--------------+--------------+------+ | 2 | what is your drink? | 5 | manhattan | 0 | 2 | | 2 | what is your drink? | 6 | cosmopolitan | 0 | 2 | +-----+------------------------+-----+--------------+--------------+------+

我已经看了几个小时的文档，任何帮助将不胜感激:)谢谢。

你的SQL查询应该是;

SELECT * FROM Questions, Answers WHERE Answers.qId = 2 GROUP BY Answers.aId;

要么

SELECT * FROM Questions, Answers WHERE Answers.qId = Questions.qId and Questions.qId = 2;

这个查询会告诉你这个;

| qId | question | aId | answer | answer_count | qId | |-----|---------------------|-----|---------------|--------------|-----| | 2 | what is your drink? | 5 | manhattan | 0 | 2 | | 2 | what is your drink? | 6 | cosmopolitan | 0 | 2 |

你应该追加这个协会;

Answer.belongsTo(Question, { "constraints": true, "foreignKey": 'qId' });

之后你应该可以像这样使用这个关系/连接;

Question .findOne({ "where": { "qId": 2 }, "include": [Answer] }) .then(function(question) { // should show question with its answers console.log(question); // should show just answers of this question console.log(question.Answers); });