node.js - Sequelize 中的多对一关联

Question

我有两个表表 1 (D) 和表 2 (P)
表 1 的每一行都可以有一个或多个从表 2(P) 映射的行
例如:D1 -> P1,P2,P3
D2 -> P1,P3
如何在 sequelize 中实现从表 1 到表 2 的多对一关联。请帮忙。

Answer 1

我在让 Sequelize 工作时也遇到了很多麻烦。这是我如何做到的。
假设您使用 sequelize-cli:
https://www.npmjs.com/package/sequelize-cli :

npm install --save-dev sequelize-cli
npx sequelize-cli init

这给了我们我们的项目
接下来使用迁移来启动我们的 D 和 P 表以及 D 和 P 模型。

npx sequelize-cli model:generate --name D --attributes firstVariable:string,secondVariable:string,thirdVariable:string
// i made p the same way

生成器所能做的就是命名表，声明变量，并给它们类型。接下来我们编辑 D 和 P 迁移文件以匹配我们想要的表。我们还配置了我们的数据库连接。一旦看到迁移文件，就可以直观地编辑它们。这是我制作的文件。

//simple as you can get changes to config.json to
{
  "development": {
    "storage" : "/wherever/im/putting/mydatabase.db",
    "dialect": "sqlite"
  }
}

//create d
'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('D', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstVariable: {
        type: Sequelize.STRING
      },
      secondVariable: {
        type: Sequelize.STRING
      },
      anotherVariable: {
        type: Sequelize.STRING
      }//,
      // createdAt: {
      //   allowNull: false,
      //   type: Sequelize.DATE
      // },
      // updatedAt: {
      //   allowNull: false,
      //   type: Sequelize.DATE
      // }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('D');
  }
};

//create P
'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('P', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstVariable: {
        type: Sequelize.STRING
      },
      secondVariable: {
        type: Sequelize.STRING
      },
      anotherVariable: {
        type: Sequelize.STRING
      },
      Did: {
        type: Sequelize.INTEGER, 
        references: {
          model: 'D',
          key: 'id'
        }
       }//,
      // createdAt: {
      //   allowNull: false,
      //   type: Sequelize.DATE
      // },
      // updatedAt: {
      //   allowNull: false,
      //   type: Sequelize.DATE
      // }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('P');
  }
};

在编辑 Create P 和 Create D 之前，运行 npx sequelize-cli db:migrate 来测试您的连接。当连接工作并且您获得表时，然后继续运行 migrate 以测试您对 Create P 和 Create D 的更改。只需删除所有表并再次运行 migrate。
第一个技巧是将适当的外键添加到迁移文件中。这是 P.Did。引用选项很重要。我认为迁移文件创建了所有的数据库结构。
接下来我们调整模型文件；注释在代码中:

//D.js
'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class D extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      D.hasMany(models.P, {foreignKey: "Did", onDelete: 'CASCADE', onUpdate: 'CASCADE'}); // https://sequelize.org/master/manual/assocs.html
    }
  };
  D.init({
    firstVariable: DataTypes.STRING,
    secondVariable: DataTypes.STRING,
    anotherVariable: DataTypes.STRING
  }, {
    sequelize,
    timestamps: false, //timestamps may need to be first option?
    modelName: 'D',
    tableName: 'D'
  });
  return D;
};

//P.js
'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class P extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      P.belongsTo(models.D, {onDelete: 'CASCADE', onUpdate: 'CASCADE', foreignKey: 'Did'}); //not setting onUpdate and onDelete, if also doesn't work on your system set in database instead
     
    }
  };
  P.init({ //I make sure all P variables are in init, but I have not test if adding the foreign key is necessary here
    firstVariable: DataTypes.STRING,
    secondVariable: DataTypes.STRING,
    anotherVariable: DataTypes.STRING,
    Did: DataTypes.INTEGER 
  }, {
    sequelize,
    timestamps: false, //otherwise sequelize assumes timestamps are in the table and writes queries accordingly
    modelName: 'P',
    tableName: 'P'
  });
  return P;
};

技巧 2 或技巧 1 的第二部分是将外键选项添加到 D.hasmany 和 P.belongsto。这避免了许多陷阱。
最后是一些用于测试的代码:

//index.js
//index.js to test code
const db = require('./models');
const asyncouterFunction = async function() {
const D = db.D;
const P = db.P;
const d1 = await D.create({firstVariable: "A", secondVariable:"B", anotherVariable: "C"});
const p1 = await P.create({firstVariable: "a", secondVariable:"b", anotherVariable: "c"});
const p2 = await P.create({firstVariable: "a", secondVariable:"b", anotherVariable: "c"});
await d1.addPs([p1,p2]);
let count = await d1.countPs();
console.log(count);
await d1.removeP(p2);
count = await d1.countPs();
console.log(count)
}
asyncouterFunction();