如何在Sequelize中创建记录用户房间首末次出现时间的新表？

现有数据表及目标需求 #

用户表（user）

房间表（room）

聊天行为数据表（chat data）

目标新表（记录用户房间停留时间）

需求说明：用户切换房间时，前一个房间的last_seen为切换时间；同一房间的多条停留记录合并为一条，记录首次和最后（切换）时间。

实现步骤 #

1. 创建新表的Sequelize模型

定义名为UserRoomStay的模型，关联用户表和房间表，添加联合唯一约束避免重复记录：

const { Model, DataTypes } = require('sequelize');
const sequelize = require('../your-sequelize-instance'); // 替换为你的Sequelize实例

class UserRoomStay extends Model {}

UserRoomStay.init({
  userId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: { model: 'user', key: 'id' }
  },
  roomId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    references: { model: 'room', key: 'id' }
  },
  first_seen: {
    type: DataTypes.BIGINT,
    allowNull: false
  },
  last_seen: {
    type: DataTypes.BIGINT,
    allowNull: false
  }
}, {
  sequelize,
  modelName: 'UserRoomStay',
  tableName: 'user_room_stays',
  indexes: [{
    unique: true,
    fields: ['userId', 'roomId']
  }]
});

// 生产环境建议用迁移文件替代sync()
UserRoomStay.sync();
module.exports = UserRoomStay;

2. 处理聊天数据生成新表记录

核心逻辑：按用户分组排序聊天记录，识别房间切换事件，计算每个房间的首次/最后停留时间：

const ChatData = require('./your-chat-data-model'); // 替换为聊天数据表模型
const UserRoomStay = require('./UserRoomStay');

async function generateRoomStayRecords() {
  // 按用户、时间顺序获取所有聊天记录
  const chatRecords = await ChatData.findAll({
    order: [['userId', 'ASC'], ['first_seen', 'ASC']]
  });

  const userCurrentRoom = new Map(); // 临时存储用户当前所在房间的初始数据

  for (const record of chatRecords) {
    const userTempData = userCurrentRoom.get(record.userId);

    if (!userTempData) {
      // 用户首次进入房间，初始化临时记录
      userCurrentRoom.set(record.userId, {
        roomId: record.roomId,
        firstSeen: record.first_seen
      });
    } else if (userTempData.roomId !== record.roomId) {
      // 用户切换房间，写入上一个房间的停留记录
      await UserRoomStay.upsert({
        userId: record.userId,
        roomId: userTempData.roomId,
        first_seen: userTempData.firstSeen,
        last_seen: record.first_seen
      });

      // 更新用户当前房间的临时数据
      userCurrentRoom.set(record.userId, {
        roomId: record.roomId,
        firstSeen: record.first_seen
      });
    }
    // 同一房间的多条记录不处理，首次时间已留存，最后时间在切换时更新
  }

  // 处理用户最后停留的房间（无后续切换记录时，用该房间最后一条聊天时间作为last_seen）
  for (const [userId, data] of userCurrentRoom.entries()) {
    const lastRecord = await ChatData.findOne({
      where: { userId, roomId: data.roomId },
      order: [['first_seen', 'DESC']]
    });

    await UserRoomStay.upsert({
      userId,
      roomId: data.roomId,
      first_seen: data.firstSeen,
      last_seen: lastRecord.first_seen
    });
  }
}

// 执行记录生成操作
generateRoomStayRecords().catch(err => console.error(err));

补充说明 #

• 用upsert方法实现"存在则更新，不存在则插入"，避免重复记录。
• 生产环境请使用Sequelize迁移文件管理表结构，不要依赖sync()。
• 如果时间字段用DATE类型，可将模型中字段类型改为DataTypes.DATE，并做时间戳与日期格式的转换。

内容的提问来源于stack exchange，提问作者Ders Çalışma zamanı