jeudi 13 août 2015

Sequelize.js One-to-Many relationship foreign key

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