如何在 Node JS 中合并其他表的响应属性

Question

我有 3 个表用户、汽车和用户汽车

User{id, name, phone, email}
Cars{id, name, manufacturer}
UserCars{id, car_id, user_id, role}
User have many cars(through UserCars)
Cars have many users(through UserCars)

我正在使用快速 js

    router.get('/', async (req, res) => {
        try {
            let car = await Car.findOne({
                where: {
                id: req.car_id
            }});
            let users = await car.getUsers({joinTableAttributes: ['role']})
            res.send(users)
        } catch (e) {
            console.log(e)
            res.status(400).send(e)
        }
    })

这是我的回应

[
    {
        "id": 1,
        "name": "test",
        "email": null,
        "phone": null,
        "createdAt": "2019-07-09T09:38:11.859Z",
        "updatedAt": "2019-07-12T04:34:20.922Z",
        "User_car": {
            "role": "driver"
        }
    }
]

但是知道如何在用户对象中包含角色，而不是在表中单独指定User_car，有没有办法让我得到以下输出

[
    {
        "id": 1,
        "name": "test",
        "email": null,
        "phone": null,
        "role": 'driver'
        "createdAt": "2019-07-09T09:38:11.859Z",
        "updatedAt": "2019-07-12T04:34:20.922Z"
    }
]

Answer 1

在获取属性时，您可以使用sequelize.literal来获取该字段。

attributtes: [
  // define your other fields
  [sequelize.literal('`users->User_car`.`role`'), 'role'],
]

现在，我不确定这是否适用于car.getUsers.我通常使用 include 执行单个查询，并定义"连接"表，以便我可以知道如何在 sequelize 上命名它。我给大家举个例子。

module.exports = (sequelize, DataTypes) => {
  const UserCar = sequelize.define('UserCar', {
    // id you don't need and id field because this is a N:M relation
    role: {
      type: DataTypes.STRING
    },
    carId: {
      field: 'car_id',
      type: DataTypes.INTEGER
    },
    userId: {
      field: 'user_id',
      type: DataTypes.INTEGER
},
  }, {
    tableName: 'User_car',
    underscored: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at',
  });
  UserCar.associate = (models) => {
    models.user.belongsToMany(models.car, { as: 'cars', through: User_car, foreignKey: 'user_id' });
    models.car.belongsToMany(models.user, { as: 'users',    through: User_car, foreignKey: 'car_id' });
  };
  return UserCar;
};
router.get('/', async (req, res) => {
  try {
    const users = await User.findAll({ 
      include: [{
        model: Car,
        as: 'cars',
        where: { id: req.car_id }
      }],
      attributtes: [
        'id',
        'name',
        'email',
        'phone',
        [sequelize.literal('`cars->User_car`.`role`'), 'role'],
      ]
    })
    res.send(users)
  } catch (e) {
    console.log(e)
    res.status(400).send(e)
  }
});