sequelize.js - Sequelize : How to get most recent unique records?

Question

我有包含用户之间消息的表。
{ message, senderId, receiverId, createdAt, updatedAt, id }
对于给定的用户，我想返回一个用户曾经与之交谈过的人的列表，并按最新消息的日期排序。
Person D, Person A, Person C, Person Z, ....
用户列有这些:{ name, createdAt, updatedAt, id }

Answer 1

查询所有消息并使用 Op.or 查询过滤器查找您的用户 ID 与发送者或接收者匹配的记录，然后按 createdAt 降序排序。使用 raw 避免创建 Instance 对象，然后循环结果并通过减少 messages 数组来收集用户 ID。

const userId = ???;
const messages = await Message.findAll({
  attributes: ['senderId', 'receiverId', 'createdAt'],
  where: {
    [Op.or]: {
      senderId: userId,
      receiverId: userId,
    },
  },
  order: [['createdAt', 'DESC']],
  raw: true, // get raw since we don't need to bother creating Instances
});

// use an object to collect unique IDs
const userIds = {};
// reduce messages to collect userIds and createdAt
const userIds = messages.reduce((userIds, message) => {
  // check the senderId and recieverId
  ['senderId', 'recieverId'].forEach((column) => {
    // if it's not our userId and not already in the list, add it
    if (message[column] !== userId && !userIds[message[column]]) {
      // you could check things about message.createdAt here, or use JSON for more info
      userIds[message[column]] = message.createdAt;
    }
  });
  // return the accumulator
  return userIds;
}, {});

console.log('User IDs', userIds);