Eggjs 联表查询

/**
 * 用户模型
 */
module.exports = app => {
  const { STRING, INTEGER } = app.Sequelize;
  const User = app.model.define('user', {
    id: {
      type: INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
    name: {
      type: STRING,
      allowNull: false
    },
    email: {
      type: STRING,
      allowNull: false
    },
    password: {
      type: STRING(32),
      allowNull: false
    },
    avatar: {
      type: STRING,
      allowNull: true
    },
    roleId: {
      type: INTEGER,
      allowNull: false,
      defaultValue: 6
    }
  });

  // 表关联的字段
  User.associate = function() {
    // 一对多
    app.model.User.hasMany(app.model.Diary, { foreignKey: 'user_id', targetKey: 'id'});
    /**
     * User.belongsTo(关联的模型, { foreignKey: '使用什么字段关联', targetKey: '与关联的模型那个字段关联', as: '别名' });
    */
    // 一对一
    app.model.User.belongsTo(app.model.Role, { foreignKey: 'roleId', targetKey: 'id', as: 'role'});
  }

  return User;
}

/**
 * 角色模型
 */
module.exports = app => {
  const { INTEGER, STRING } = app.Sequelize;
  const Role = app.model.define('role', {
    id: {
      type: INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    name: STRING(50),
    pid: INTEGER
  }, {
    timestamps: false
  });
 
  return Role;
}

/**
 * 日志模型
 */
module.exports = app => {
  const { STRING, INTEGER } = app.Sequelize;
  const Diary = app.model.define('diary', {
    id: {
      type: INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
    title: {
      type: STRING,
      allowNull: false
    },
    content: {
      type: STRING,
      allowNull: false
    }
  });

  // 表关联的字段
  Diary.associate = function() {
    app.model.Diary.belongsTo(app.model.User, { foreignKey: 'user_id', targetKey: 'id'})
  }

  return Diary;
}

// 获取用户列表/详情
async detail(id) {
  const { app } = this;
  try {
    if(id){
      // return await app.model.User.findByPk(app.toInt(id));
      return await app.model.User.findOne({
        attributes: ['id','name','email','avatar','roleId'],
        where: { id },
        include: [{
          model: app.model.Role,
          as: 'role',//这里的 as需要与之前定义的as名字相同
        }]
      });
    }
    return await app.model.User.findAll({
      attributes: ['id','name','email','avatar','roleId'],
      include: [{
        model: app.model.Role,
        as: 'role',//这里的 as需要与之前定义的as名字相同
      },{
        model: app.model.Diary
      }]
    });
  }catch(err){
    console.log(err);
    return null;
  }
}