node.js - 如何获取每个用户的对话并包含消息？

Question

我试图让每个用户的对话显示在我前端的收件箱屏幕上。我能够将消息发布到数据库，但是当我尝试获取它们时，我不断收到

[SequelizeEagerLoadingError]: Message is not associated to Conversation!

有人可以看看我的代码。
这是我的获取请求:

router.get('/', auth, (req, res) => {
  Conversation.findAll({
    where: {
      [Op.or]: [
        {
          user1: { [Op.eq]: req.user.id },
          user2: { [Op.eq]: req.user.id },
        },
      ],
    },
    include: [{ model: Message }],
  })
    .then(() => res.send())
    .catch((err) => console.log(err));
});

这是我的对话模型

const Conversation = db.define('Conversation', {
});

module.exports = Conversations;

Conversation.associate = (models) => {
  Conversation.hasMany(models.Message, {
    foreignKey: 'conversationId',
  });

  Conversation.belongsTo(models.Post, {
    foreignKey: 'PostId',
  });
};

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

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

这是我的消息模型:

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

module.exports = Message;

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

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

Message.associate = (models) => {
  Message.belongsTo(models.User, {
    foreignKey: 'senderId',
    as: 'Sender',
  });

  Message.belongsTo(models.User, {
    foreignKey: 'receiverId',
    as: 'Receiver',
  });

  Message.belongsTo(models.Conversation, {
    foreignKey: 'conversationId',
  });
};

Post.hasMany(Conversation, { foreignKey: 'PostId' });

更新
删除后

Conversation.hasMany(models.Message,{foreginKey:"conversationId"}) 

从我的对话模型中的关联方法并将其添加到我的消息模型中，我不再收到关联错误，但是现在当我尝试测试 get 请求时，当我的数据库充满条目时，我一直收到空响应。
这是我更新的获取请求:

router.get('/',auth,async(req,res)=>{
const conversation = await Conversations.findAll({
include:[{
 model:Message,
required: false,
}],
where:{
[Op.or]:[{
user1:{[Op.eq]:req.user.id},
user2:{[Op.eq]:req.user.id},
}]
},
});

if(!conversation) return res.send().catch(err=>console.log(err)) 

res.status(200).send()

})

这是我在终端中的回复:

 Executing (default): SELECT "Conversation"."id", 
 "Conversation"."createdAt", "Conversation"."updatedAt", 
 "Conversation"."user1", "Conversation"."user2", 
 "Conversation"."PostId", "Messages"."id" AS "Messages.id", 
 "Messages"."senderId" AS "Messages.senderId", 
 "Messages"."receiverId" AS 
 "Messages.receiverId", 
 "Messages"."message" AS "Messages.message", 
 "Messages"."conversationId" AS "Messages.conversationId", 
 "Messages"."createdAt" AS "Messages.createdAt", 
 "Messages"."updatedAt" AS "Messages.updatedAt" FROM "Conversations" 
 AS "Conversation" LEFT OUTER 
 JOIN "Messages" AS "Messages" ON "Conversation"."id" = 
 "Messages"."conversationId" WHERE (("Conversation"."user1" = 43 AND 
 "Conversation"."user2" = 43));

Answer 1

const { user } = require("./containers/api/config/database")
您可以进行一些改进 - 您的示例代码中的问题是您在 Messages 和 User 之间有多个关系，因此您需要指定 as 关键字，以便 Sequelize 知道要使用哪个。没有关联中的 as 就没有任何关联，就 Sequelize 可以看出没有任何关系。
我会让 Sequelize 自动为主键和外键生成大部分列以保持简单。当可以返回多个结果时，使用单数表名并让 Sequelize 将它们复数化也是一种很好的做法，例如 Post.getConversations() 与 Message.getConversation() 。如果可以的话，使用 async/await 而不是 thenables 也更具可读性。
您应该能够将 where 移动到 Message 包含并添加 required: true 以使其成为 RIGHT JOIN 。
模型

const User = db.define('user', {
  // fields go here
});

const Post = db.define('post', {
  // fields go here
});

const Conversation = db.define('conversation', {
  // don't define columns, they will be generated
});

const Message = db.define('message', {
  message: {
    type: Sequelize.STRING,
  },
  // more fields
});

关联

// Message -- Conversation, creates message.conversationId NOT NULL
Message.belongsTo(Conversation, { allowNull: false });
// Conversation -< Messages, uses message.conversationId
Conversation.hasMany(Message);

// Conversation -- User, creates conversation.creatorId NOT NULL
Conversation.belongsTo(User, { as: 'Creator', foreignKey: 'creatorId', allowNull: false });
// User -< Conversations, uses conversation.creatorId
User.hasMany(Conversation, { as: 'CreatedConversations', foreignKey: 'creatorId' });

// Message -- User, creates message.senderId NOT NULL
Message.belongsTo(User, { as: 'Sender', foreignKey: 'senderId', allowNull: false });
// User -< Message (Sent), join user.id = message.senderId
User.hasMany(Message, { as: 'Sent', foreignKey: 'senderId' });

// Message -- User, creates message.receiverId NOT NULL
Message.belongsTo(User, { as: 'Receiver', foreignKey: 'receiverId', allowNull: false });
// User -< Message (Received), join user.id = message.receiverId
User.hasMany(Message, { as: 'Received', foreignKey: 'receiverId' });

// Conversation -- Post, creates conversation.postId NOT NULL
Conversation.belongsTo(Post, { allowNull: false });
// Post -< Conversations, uses conversation.postId
Post.hasMany(Conversation);

查询
获取用户作为发送者或接收者在消息中的所有对话。

router.get('/', auth, async (req, res) => {
  try {
    const conversations = await Conversation.findAll({
      include: [{
        model: Message,
        where: {
          [Op.or]: [
            { senderId: req.user.id }, 
            { receiverId: req.user.id },
          ],
        },
        required: true, // RIGHT  JOIN
      }],
    });
    return res.json(conversations);
  } catch (err) {
    console.log(err);
    return res.status(500).send(err);
  }
});

查询
获取用户创建的所有对话。这将替换对话中的 user1 和 user2，但您可能仍希望将其用于其他用途。

router.get('/created_conversations', auth, async (req, res) => {
 try {
   const conversations = await Conversation.findAll({
     include: [{
       model: Message,
       required: false, // LEFT  JOIN
     }],
     where: {
       creatorId: req.user.id
     },
   });
   return res.json(conversations);
 } catch (err) {
   console.log(err);
   return res.status(500).send(err);
 }
});

router.get('/user', auth, async (req, res) => {
  try {
    const user = await User.findByPk(req.user.id, {
      include: [{
        model: Conversation,
        as: 'CreatedConversations',
        required: false, // LEFT  JOIN
        include: [{
          model: Message,
          required: false, // LEFT  JOIN
        }],
      }],
    });

    // const conversations = user.getConversations();
    // const [conversation] = conversations; // get the first result
    // const messages = conversation.getMessages();

    return res.json(user);
  } catch (err) {
    console.log(err);
    return res.status(500).send(err);
  }
});