如何将关联添加到从Sequelize-Auto生成的模型

Question

对于现有的MySQL数据库，我使用Sequelize-auto package生成模型。但是这些关联并不与模型类一起出现。

我有一个MySQL数据库，我正在为NodeJS web项目使用它。另外，我使用Sequelize作为ORM。由于数据库已经存在，我希望将模型类生成为实体。所以我使用了sequelize-auto

https://github.com/sequelize/sequelize-auto来生成模型类。但是，当它们被生成时，属性已经被正确设置，但是关联没有出现在模型类中。因此，我在从数据库获取数据时遇到了问题。

以下是使用sequlize-auto生成的两个模型类。数据库中有两个表，分别名为部门表和类别表。department.js和category.js是生成的两个模型类

department.js

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('department', {
        department_id: {
            type: DataTypes.INTEGER(11),
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        name: {
            type: DataTypes.STRING(100),
            allowNull: false
        },
        description: {
            type: DataTypes.STRING(1000),
            allowNull: true
        }
    }, {
        tableName: 'department',
        timestamps: false,
    });
};

category.js

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('category', {
        category_id: {
            type: DataTypes.INTEGER(11),
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        },
        department_id: {
            type: DataTypes.INTEGER(11),
            allowNull: false
        },
        name: {
            type: DataTypes.STRING(100),
            allowNull: false
        },
        description: {
            type: DataTypes.STRING(1000),
            allowNull: true
        }
    }, {
        tableName: 'category',
        timestamps: false,
    });
};

因此，为了成功获取关联和获取数据，还需要做些什么。有人能帮我一下吗。表结构如下。

​

Answer 1

1)在models文件夹中创建一个index.js文件并添加以下代码

import Sequelize from 'sequelize';

const fs = require('fs');
const path = require('path');

const basename = path.basename(__filename);

const db = {};

// @ts-ignore
const sequelize = new Sequelize('dbname', 'dbUser', 'password', {
  host: '127.0.0.1',
  port: 'PORT',
  dialect: 'mysql',
  define: {
    freezeTableName: true,
    timestamps: false,
  },
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000,
  },
  // <http://docs.sequelizejs.com/manual/tutorial/querying.html#operators>
  operatorsAliases: false,
});

const tableModel = {};

fs.readdirSync(__dirname)
  .filter(file => file.indexOf('.') !== 0 && file !== basename && file.slice(-3) === '.js')
  .forEach(file => {
    const model = sequelize.import(path.join(__dirname, file));
    db[model.name] = model;
    tableModel[model.name] = model;
  });

Object.getOwnPropertyNames(db).forEach(modelName => {
  const currentModel = db[modelName];
  Object.getOwnPropertyNames(currentModel.rawAttributes).forEach(attributeName => {
    if (
      Object.prototype.hasOwnProperty.call(
        currentModel.rawAttributes[attributeName],
        'references'
      ) &&
      Object.prototype.hasOwnProperty.call(
        currentModel.rawAttributes[attributeName].references,
        'model'
      ) &&
      Object.prototype.hasOwnProperty.call(
        currentModel.rawAttributes[attributeName].references,
        'key'
      )
    ) {
      if (
        !(
          currentModel.rawAttributes[attributeName].references.model &&
          currentModel.rawAttributes[attributeName].references.key
        )
      ) {
        console.log(
          `*SKIPPED* ${modelName} ${attributeName} references a model ${currentModel.rawAttributes[attributeName].references.model} with key ${currentModel.rawAttributes[attributeName].references.key}`
        );
        return;
      }

      console.log(
        `${modelName} ${attributeName} references a model ${currentModel.rawAttributes[attributeName].references.model} with key ${currentModel.rawAttributes[attributeName].references.key}`
      );
      const referencedTable =
        tableModel[currentModel.rawAttributes[attributeName].references.model];

      currentModel.belongsTo(referencedTable, { foreignKey: attributeName });
      referencedTable.hasMany(currentModel, { foreignKey: attributeName });

    }
  });
});

// @ts-ignore
db.sequelize = sequelize;
// @ts-ignore
db.Sequelize = Sequelize;

// eslint-disable-next-line eol-last
module.exports = db;

2)在你的解析器中只需引用上面的代码：

const db = require('../assets/models/index');

Answer 2

添加到他基于here的@CodingLittle答案中。

为了进行多对多关联，我添加了以下内容：

enum Junctions {
  user = 'user',
  roles = 'roles',
}

enum JuntiontThrough {
  userroles = 'userroles',
}


interface JunctionObject {
  junctionBelongsTo?: any;
}

const checkIfAttrExists= (obj, value) => {
  return Object.prototype.hasOwnProperty.call(obj, value);
};

const checkRefrence = (obj, attr, value) => {
  return obj.rawAttributes[attr].references[value];
};


export const getJunction = (junc: Junctions): JunctionObject => {
  const junctions = {
    user: {
      junctionBelongsTo: [
        { key: Junctions.roles, value: juntiontThrough.userroles }
      ],
    },
    roles: {
      junctionBelongsTo: [{ key: Junctions.user, value: juntiontThrough.userroles }],
    },
  }[junc];

  if (!junctions) return {};

  return junctions;
};


  const models = Object.getOwnPropertyNames(db);

models.forEach(modelName => {
  const currentModel = db[modelName];
  const junction = getJunction(modelName as Junctions);

  if (!_.isEmpty(junction)) {
    // eslint-disable-next-line array-callback-return
    junction.junctionBelongsTo.reduce((key, value) => {
      currentModel.belongsToMany(db[value.key], {
        through: db[value.value],
      });
    }, {});
  }

  const attributes = Object.getOwnPropertyNames(currentModel.rawAttributes);

  attributes.forEach(attributeName => {
    if (
      checkIfAttrExists(currentModel.rawAttributes[attributeName], 'references') &&
      checkIfAttrExists(currentModel.rawAttributes[attributeName].references, 'model') &&
      checkIfAttrExists(currentModel.rawAttributes[attributeName].references, 'key')
    ) {
      if (
        !(
          checkRefrence(currentModel, attributeName, 'model') &&
          checkRefrence(currentModel, attributeName, 'key')
        )
      ) {
        return;
      }

      const referencedTable =
        tableModel[currentModel.rawAttributes[attributeName].references.model];

      if (!(modelName.toString() in juntiontThrough)) {
        console.log(
          `${modelName} ${attributeName} references a model ${currentModel.rawAttributes[attributeName].references.model} with key ${currentModel.rawAttributes[attributeName].references.key}`
        );
        currentModel.belongsTo(referencedTable, { foreignKey: attributeName });
        referencedTable.hasMany(currentModel, { foreignKey: attributeName });
      }
    }
  });
})

请注意，要使多对多关系起作用，必须像我在getJunction函数中所做的那样手动添加关系

Answer 3

我遵循了在sequelize-auto (init-models.ts)上的typescript示例中演示的模式-在现有的initModels函数中，在js中工作得很好。

​

export function initModels(sequelize: Sequelize) {
  Product.initModel(sequelize);
  Supplier.initModel(sequelize);
  
  Supplier.hasMany(Product, { as: "products", foreignKey: "supplierId"});
  
  return {...

​