node.js - NodeJS & Sequelize : showing all the conversation the current user has

Question

我目前正在尝试从我的所有对话屏幕实现我的后端。我正在查找所有对话并包括与每个对话相关的所有消息。
在我的 React Native 前端，在我的所有对话屏幕中，我将所有对话显示为一个平面列表，并通过从消息列表中挑选出来来显示 2 个用户之间发送的最后一条消息。
我应该继续使用这种方法，还是在 session 表中包含指向最后一条消息的链接并在每次在 session 中发送新消息时更新它在性能方面会更好？如果是这样，我如何更改我的代码来这样做？
对话模型:

const Conversation = db.define("Conversation", {},
{
paranoid: true, 
timestamps: true, 
deletedAt: "deletedAt",
}
);

module.exports = Conversation;

User.hasMany(Conversation, {
foreignKey: 'user1'
});

User.hasMany(Conversation, {
foreignKey: 'user2'
});

Conversation.belongsTo(User, { as: 'Creator', foreignKey: 'user1', allowNull: false })
Conversation.belongsTo(User, { as: 'Recipient', foreignKey: 'user2', allowNull: false })

消息模型:

const Message = db.define("Message", {
senderId: {
type: Sequelize.STRING,
},
receiverId: {
type: Sequelize.STRING,
},
message: {
type: Sequelize.STRING,
},
conversationId:{
type: Sequelize.INTEGER,
}
});

module.exports = Message;

User.hasMany(Message, {
foreignKey: 'senderId',
});

User.hasMany(Message, {
foreignKey: 'receiverId',
});

  Message.associate = (models) => {
  Message.belongsTo(models.Conversations,{
  foreignKey: "conversationId",
  }
  ); 
  };

Conversation.hasMany(Message,{
foreignKey: "conversationId",
}
);

Message.belongsTo(User, {
as:"Sender",
foreignKey: "senderId",
allowNull: false 
});

Message.belongsTo(User, {
as:"Receiver",
foreignKey: "receiverId",
allowNull: false 
});

获取所有对话查询:

router.get('/', auth, async (req, res) => {
const conversations = await Conversation.findAll({
  order: [[Message,"createdAt", "DESC"]],
  include: [
    {
    model: Message,
    where: {
      [Op.or]: [
        { senderId: req.user.id }, 
        { receiverId: req.user.id },
      ],
    },
    required: true, // RIGHT  JOIN
  }]
  })
 

if (!conversations) return res.status(404).send();

    res.status(200).send();
});

Answer 1

您可以尝试将每个对话中包含的消息限制为仅最后一条消息

const conversations = await Conversation.findAll({
  order: [[Message,"createdAt", "DESC"]],
  include: [
    {
    model: Message,
    order: [["createdAt", "DESC"]],
    limit: 1,
    where: {
      [Op.or]: [
        { senderId: req.user.id }, 
        { receiverId: req.user.id },
      ],
    },
    required: true, // RIGHT  JOIN
  }]
  })

如果上述查询不起作用，您始终可以尝试在对话中存储最后一条消息 ID，但如果您存储一条消息而忘记更新指向最后一条消息的链接或删除最后一条消息，这可能会导致不一致消息，再次忘记更新对话中的链接。