CODEWITHSUNDEEP
javascriptmysqlnode.jsexpresssequelize.js
AustinWo
AustinWo

Reputation: 11

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.

Upvotes: 1

Views: 903

Answers (1)

Ali BARIN
Ali BARIN

Reputation: 1920

Your sql query should be;

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

or

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

This query will show you this;

| 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 |

and you should append this association;

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

After you should be able to use this relation/join like this;

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

Upvotes: 1

Related Questions