mysql - Sequelize 填充连接表属性

Question

我有几张 table ，我想在连接的 table 上做一些包含，但我似乎无法弄清楚。这是我的模型:

/* Staff model */

const model = {
  fisrName: {
    type: DataTypes.INTEGER,
    references: { model: 'Roles', key: 'id' },
  },
  lastName: {
    type: DataTypes.INTEGER,
    references: { model: 'Profiles', key: 'id' },
  }
};

const Staff = createModel('Staff', model, { paranoid: true });
export default Staff

/* Service model */
const model = {
  name: {
    type: DataTypes.STRING,
  },
  category: {
    type: DataTypes.STRING,
  },
  description: {
    type: DataTypes.STRING,
  }
};

const Service = createModel('Service', model, {});

export default Service;

/* Appointment model */
const model = {
  endDate: {
    type: DataTypes.DATE,
  },
  startDate: {
    type: DataTypes.DATE,
  },
  day: {
    type: DataTypes.DATE,
  },
};

const Appointment = createModel('Appointment', model, {})

Appointment.belongsToMany(Service, { through: 'Products', as: 'products' });

export default Appointment;

/* Products model */
const model = {
  serviceId: {
    type: DataTypes.INTEGER,
  },
  appointmentId: {
    type: DataTypes.INTEGER,
  },
  staffId: {
    type: DataTypes.INTEGER,
    references: { model: 'Staff', key: 'id' },
  }
};

const Product = createModel('Product', model, {});

Product.belongsTo(Staff, { foreignKey: 'staffId', as: 'staff' });

export default Product;

这是我的约会查询，我在其中包含了 services 数组，在这个 services 数组上，我有一个 Products 对象，在这个对象中我有一个要填充的 staffId，但我不确定如何填充。我尝试了不同的方法，但没有任何效果。

const appointment = await Appointment.findByPk(req.params.id, {
      include: [
        {
          model: Service,
          as: 'services',
          through: { attributes: { include: ['id', 'staffId', 'serviceId', 'appointmentId'], exclude: ['createdAt', 'updatedAt', 'AppointmentId', 'ServiceId'] },
        },
      ],
    });

这是我的回应:

{
    "startDate": "date",
    "endDate": "date",
    "day": "date",
    "services": [{
        "id": 1,
        "name": "Service name",
        "category": "service category",
        "description": "service description",
        "Products": {
            "id": 1,
            "staffId": 2,
            "serviceId": 1,
            "appointmentId": 1
        }
    }]
}

我想要做的是使用集合中的模型填充 Products 中的 StaffId ，如下所示:

{
    "startDate": "date",
    "endDate": "date",
    "day": "date",
    "services": [{
        "id": 1,
        "name": "Service name",
        "category": "service category",
        "description": "service description",
        "Products": {
            "id": 1,
            "staffId": {
                "firstName": "First Name",
                "lastName": "Last Name"
            },
            "serviceId": 1,
            "appointmentId": 1
        }
    }]
}

Answer 1

const appointment = await Appointment.findByPk(req.params.id, {
    include: [
        {
            model: Service,
            as: 'services',
            through: {
                attributes: { include: ['id', 'staffId', 'serviceId', 'appointmentId'], exclude: ['createdAt', 'updatedAt', 'AppointmentId', 'ServiceId'] },
            },
            include: [{
                as: 'Products', model: Product,
                include: 'staff'
            }]
        }
    ]
});