如何在Sequelize中查询历史表的当前记录及上一条关联数据

我有一张history表，当其他表执行数据新增或更新操作时，相关数据会存入该表。现在需要根据tableName和tableId字段，按createdOn降序，查询特定数据行及其紧邻的上一条数据。

History表模型定义 #

const { STRING, BOOLEAN, INTEGER } = require("sequelize");

module.exports = (sequelize, DataTypes) => {
    const History = sequelize.define("history", {
        id: { type: INTEGER, primaryKey: true },
        tableName: { type: STRING },
        tableId: { type: STRING },
        data: { type: STRING },
        createdOn: { type: INTEGER, allowNull: true }
    }, {
        timestamps: false,
        freezeTableName: true,
    })
    return History;
}

解决方案 #

以下提供两种实现方式，可根据实际场景选择：

方式一：使用窗口函数（单次查询，性能更优）

利用SQL的LAG()窗口函数，在同tableName和tableId的分组内，直接获取当前记录的上一条历史数据，仅需一次数据库查询：

async function getHistoryWithPrevious(tableName, tableId) {
  const [result] = await sequelize.query(`
    SELECT 
      h.id,
      h.tableName,
      h.tableId,
      h.data,
      h.createdOn,
      LAG(h.id) OVER (PARTITION BY h.tableName, h.tableId ORDER BY h.createdOn DESC) AS previousId,
      LAG(h.data) OVER (PARTITION BY h.tableName, h.tableId ORDER BY h.createdOn DESC) AS previousData,
      LAG(h.createdOn) OVER (PARTITION BY h.tableName, h.tableId ORDER BY h.createdOn DESC) AS previousCreatedOn
    FROM history h
    WHERE h.tableName = :tableName AND h.tableId = :tableId
    ORDER BY h.createdOn DESC
    LIMIT 1;
  `, {
    replacements: { tableName, tableId },
    type: sequelize.QueryTypes.SELECT
  });

  if (!result) return null;

  return {
    id: result.id,
    tableName: result.tableName,
    tableId: result.tableId,
    data: result.data,
    createdOn: result.createdOn,
    previousHistory: result.previousId ? {
      id: result.previousId,
      tableName: result.tableName,
      tableId: result.tableId,
      data: result.previousData,
      createdOn: result.previousCreatedOn
    } : null
  };
}

方式二：两次查询（逻辑直观，可读性强）

先查询出最新的记录，再基于该记录的createdOn，查询同组内时间更早的最新记录：

async function getHistoryWithPrevious(tableName, tableId) {
  // 获取指定tableName和tableId下的最新记录
  const latestHistory = await History.findOne({
    where: { tableName, tableId },
    order: [['createdOn', 'DESC']]
  });

  if (!latestHistory) return null;

  // 获取该记录的上一条历史数据
  const previousHistory = await History.findOne({
    where: {
      tableName,
      tableId,
      createdOn: { [sequelize.Op.lt]: latestHistory.createdOn }
    },
    order: [['createdOn', 'DESC']]
  });

  // 组装返回结构
  return {
    ...latestHistory.toJSON(),
    previousHistory: previousHistory ? previousHistory.toJSON() : null
  };
}

预期返回结果 #

{
    "id": 10,
    "tableName": "product",
    "tableId": "155",
    "data": "{some data}",
    "createdOn": 99999999,
    "previousHistory": {
        "id": 3,
        "tableName": "product",
        "tableId": "155",
        "data": "{some data}",
        "createdOn": 7777777
    }
}

内容的提问来源于stack exchange，提问作者KIRAN K J