Sequelize关联表

关联表

示例：现在要关联两张表:system 跟 user_sytem,

model

'use strict';

module.exports = app => {
  const { STRING, DATE, INTEGER } = app.Sequelize;
  const system = app.model.define('system_info',
    {
      id: { type: INTEGER, primaryKey: true, autoIncrement: true },
      system_id: { type: STRING },
      name: { type: STRING },
      description: { type: STRING },
      token: { type: STRING },
      created_time: { type: DATE },
    },
    {
      freezeTableName: true,
      timestamps: false,
    });
  system.associate = () => { // 建立关联
    system.hasOne(app.model.UserSystem, {
      foreignKey: 'id', // 在 UserSystem 的外键名称
      constraints: false, // 这个属性非常重要，可以用来表示这个关联关系是否采用外键关联。在大多数情况下我们是不需要通过外键来进行数据表的物理关联的，直接通过逻辑进行关联即可；
    });
  };
  return system;
};

'use strict';

module.exports = app => {
  const { STRING, INTEGER } = app.Sequelize;
  const userSystem = app.model.define('user_system',
    {
      uid: { type: STRING, primaryKey: true },
      id: { type: INTEGER, primaryKey: true },
      system_id: { type: STRING, primaryKey: true },
    },
    {
      freezeTableName: true,
      timestamps: false,
    });
  return userSystem;
};

service:

进行查询

const systems = await ctx.model.System.findAndCountAll({
    attributes: [ 'id', 'name', 'description', 'created_time' ],
    limit,
    offset,
    include: [{
        model: ctx.model.UserSystem,
        attributes: [], // 一定需要的,不然select的列会包含进去
        where: { uid },
    }],
});

执行原始SQL，防注入

const sequelize = require('sequelize');
const sql = `select * from user where id = ? and age>?`;
await ctx.model.query(sql,
                     { replacements: [1,18],type: sequelize.QueryTypes.SELECT });