Sequelize - 关联 4 个表 [英] Sequelize - Associating 4 tables

问题描述

我正在尝试关联 4 个表.任务、任务问题、问题和选​​项.

我的模型如下

任务模型:

var Sequelize = require('sequelize');module.exports = function(sequelize, DataTypes) {var Task = sequelize.define('Task', {任务 ID:{类型:Sequelize.STRING，主键:真},任务名称: {类型:Sequelize.STRING，allowNull: 真},任务描述: {类型:Sequelize.STRING，allowNull: 真}},{类方法:{关联:功能(模型){Task.belongsToMany(models.Question, {通过: {模型:models.TaskQuestion},外键:'task_id'})}}});返回任务；};

任务问题模型:

var Sequelize = require('sequelize');module.exports = function(sequelize, DataTypes) {var TaskQuestion = sequelize.define('TaskQuestion', {tq_id:{类型:Sequelize.STRING，主键:真}});返回任务问题；};

问题模型:

var Sequelize = require('sequelize');module.exports = function(sequelize, DataTypes) {var Question = sequelize.define('问题', {问题 ID:{类型:Sequelize.STRING，主键:真},问题描述:{类型:Sequelize.STRING，allowNull: 真},问题类型: {类型:Sequelize.STRING，allowNull: 真}},{类方法:{关联:功能(模型){Question.hasMany(models.Option, {外键:{名称:'question_id'，allowNull: 假}}),Question.belongsToMany(models.Task, {通过: {模型:models.TaskQuestion},外键:'question_id'})}}});返回问题；};

选项模型:

var Sequelize = require('sequelize');module.exports = function(sequelize, DataTypes) {var Option = sequelize.define('Option', {option_id:{类型:Sequelize.STRING，主键:真},问题 ID:{类型:Sequelize.STRING，allowNull: 真},选项描述:{类型:Sequelize.STRING，allowNull: 真},选项类型:{类型:Sequelize.STRING，allowNull: 真}},{类方法:{}});返回选项；};

当我尝试检索数据时

router.get('/:task_id', function(req, res) {模型.Task.findOne({在哪里: {task_id:req.params.task_id},包括:[模型.问题]}).那么(功能(任务){res.json(任务)；});});

我得到的只是任务和问题之间的关联.当我单独检索问题时，我会在其下获得选项.但似乎无法一次全部检索.

有没有可能.让我知道我是否遵循了以这种格式设计数据库的正确方法.

我需要一个任务来包含多个问题，并且相同的问题可以出现在多个任务中.每个问题应包含多个选项.

解决方案

是的，这是可能的，并且在 http://docs.sequelizejs.com/en/latest/docs/models-usage/#nested-eager-loading>

基本上，不是将 models.Question 之类的模型放入包含数组中，而是放入一个对象，其中包含 model 的键和嵌套的 <代码>包含的

对于你上面的例子，这样的事情应该可以解决问题:

router.get('/:task_id', function(req, res) {模型.Task.findOne({在哪里: {task_id:req.params.task_id},包括: [{型号:models.Question，包括:[models.Option]}]}).那么(功能(任务){res.json(任务)；});});

Am trying to associate 4 tables. Tasks, TaskQuestions, Questions and Options.

My models are as follows

Tasks model:

var Sequelize = require('sequelize');

module.exports = function(sequelize, DataTypes) {
  var Task = sequelize.define('Task', {
    task_id: {
      type: Sequelize.STRING,
      primaryKey: true
    },
    task_name: {
      type: Sequelize.STRING,
      allowNull: true
    },
    task_description: {
      type: Sequelize.STRING,
      allowNull: true
    }
  },{
    classMethods: {
      associate: function(models) {
        Task.belongsToMany(models.Question, {
          through: {
            model: models.TaskQuestion
          },
          foreignKey: 'task_id'
        })
      }
    }
  });
  return Task;
};

TaskQuestions model:

var Sequelize = require('sequelize');

module.exports = function(sequelize, DataTypes) {
  var TaskQuestion = sequelize.define('TaskQuestion', {
    tq_id: {
      type: Sequelize.STRING,
      primaryKey: true
    }
  });
  return TaskQuestion;
};

Questions model:

var Sequelize = require('sequelize');

module.exports = function(sequelize, DataTypes) {
  var Question = sequelize.define('Question', {
    question_id: {
      type: Sequelize.STRING,
      primaryKey: true
    },
    question_description: {
      type: Sequelize.STRING,
      allowNull: true
    },
    question_type: {
      type: Sequelize.STRING,
      allowNull: true
    }
  },{
    classMethods: {
      associate: function(models) {
        Question.hasMany(models.Option, {
          foreignKey: {
            name: 'question_id',
            allowNull: false
          }
        }),
        Question.belongsToMany(models.Task, {
            through: {
                model: models.TaskQuestion
            },
            foreignKey: 'question_id'
        })
      }
    }
  });
  return Question;
};

Options model:

var Sequelize = require('sequelize');

module.exports = function(sequelize, DataTypes) {
  var Option = sequelize.define('Option', {
    option_id: {
      type: Sequelize.STRING,
      primaryKey: true
    },
    question_id: {
      type: Sequelize.STRING,
      allowNull: true
    },
    option_description: {
      type: Sequelize.STRING,
      allowNull: true
    },
    option_type: {
      type: Sequelize.STRING,
      allowNull: true
    }
  },{
    classMethods: {

    }
  });
  return Option;
};

When I try to retrieve the data

router.get('/:task_id', function(req, res) {
  models.Task.findOne({
    where: {
      task_id: req.params.task_id
    },
    include: [ models.Question ]
  }).then(function(task) {
    res.json(task);
  });
});

All I get is the association between Task and Questions. When I retrieve Questions individually I get Options under it. But can't seem to retrieve all at once.

Is it even possible. And let me know if am following the right approach designing the database in this format.

I need one task to contain multiple questions and same questions can appear in multiple tasks. Each question should contain multiple options.

解决方案

Yes, it's possible, and is covered in the documentation at http://docs.sequelizejs.com/en/latest/docs/models-usage/#nested-eager-loading

Basically, rather than putting a model like models.Question in your include array, you put an object, with a key for model and a key for the nested include's

For your example above, something like this should do the trick:

router.get('/:task_id', function(req, res) {
  models.Task.findOne({
    where: {
      task_id: req.params.task_id
    },
    include: [{
      model: models.Question,
      include: [models.Option]
    }]
  }).then(function(task) {
    res.json(task);
  });
});

这篇关于Sequelize - 关联 4 个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！