I am creating a survey app using Node.js/Express and MySQL with Sequelize.js ORM.
I am having trouble setting the relationship between the 2 models correctly. I'd like to have the Questions' qId foreign key in the Answers Table.
// 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');
});
But when I do MySQL queries:
select * from Questions, Answers where Answers.qId=2;
it's showing the following:
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 |
+-----+------------------------+-----+--------------+--------------+------+
When I'd like it to show
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 |
+-----+------------------------+-----+--------------+--------------+------+
I've been looking at the documentation for a few hours now and any help would be much appreciated :) Thank you.
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire