如何在Sequelize中获取hasMany关联的最新单条记录

模型关联定义：

KbDevice.hasMany(KbDeviceEvent, { foreignKey: 'kb_device_id', as: 'events' })

当前查询代码：

const allKbDevice = await KbDevice.findAll({
  attributes: ['id', 'make', 'model'],
  include: [
    {
      model: KbDeviceEvent,
      as: 'events',
      attributes: ['id', 'event', 'chamber_id','created_at'],
    },
  ],
});

需求：获取每个KbDevice对应的最新一条KbDeviceEvent记录（基于created_at字段），返回结果中该事件为单个对象而非数组。

当前返回示例：

{
  "id": "d75f9f96-a468-4c95-8098-a92411f8e73c",
  "make": "Toyota",
  "model": "Lexus",
  "events": [
    {
      "id": "a737bf7b-2b2e-4817-b351-69db330cb5c7",
      "event": "deposit",
      "chamber_id": 2,
      "created_at": "2022-09-01T22:36:19.849Z"
    }
  ]
}

期望返回示例：

{
  "id": "d75f9f96-a468-4c95-8098-a92411f8e73c",
  "make": "Toyota",
  "model": "Lexus",
  "event": {
    "id": "a737bf7b-2b2e-4817-b351-69db330cb5c7",
    "event": "deposit",
    "chamber_id": 2,
    "created_at": "2022-09-01T22:36:19.849Z"
  }
}

方法一：子查询筛选最新事件 #

直接在include中通过子查询定位每个设备的最新事件，修改as为目标字段名，并限制返回数量：

const allKbDevice = await KbDevice.findAll({
  attributes: ['id', 'make', 'model'],
  include: [
    {
      model: KbDeviceEvent,
      as: 'event',
      attributes: ['id', 'event', 'chamber_id','created_at'],
      where: {
        created_at: {
          [Sequelize.Op.eq]: Sequelize.literal(`(SELECT MAX(created_at) FROM kb_device_events WHERE kb_device_id = KbDevice.id)`)
        }
      },
      required: false, // 允许无事件的设备返回，需保留则设为true
      limit: 1
    }
  ]
});

方法二：定义一对一关联（推荐） #

在模型中新增一对一关联，专门映射最新事件，后续查询更简洁：

// 在KbDevice模型中添加关联
KbDevice.hasOne(KbDeviceEvent, {
  foreignKey: 'kb_device_id',
  as: 'event',
  scope: {
    [Sequelize.Op.and]: Sequelize.literal(`created_at = (SELECT MAX(created_at) FROM kb_device_events WHERE kb_device_id = KbDevice.id)`)
  }
});

// 查询代码
const allKbDevice = await KbDevice.findAll({
  attributes: ['id', 'make', 'model'],
  include: [
    {
      model: KbDeviceEvent,
      as: 'event',
      attributes: ['id', 'event', 'chamber_id','created_at'],
      required: false
    }
  ]
});

方法三：查询后结果处理 #

如果数据库查询方案遇到兼容问题，可先获取全量数据，再通过代码过滤：

const allKbDevice = await KbDevice.findAll({
  attributes: ['id', 'make', 'model'],
  include: [
    {
      model: KbDeviceEvent,
      as: 'events',
      attributes: ['id', 'event', 'chamber_id','created_at'],
      order: [['created_at', 'DESC']] // 按时间倒序，最新事件排在首位
    },
  ],
});

// 转换结果格式
const processedDevices = allKbDevice.map(device => {
  const deviceData = device.toJSON();
  return {
    ...deviceData,
    event: deviceData.events[0] || null,
    events: undefined // 移除原数组字段
  };
});

内容的提问来源于stack exchange，提问作者Riza Khan