node.js - Sequelize (PostgresQL) 多对多聊天实现

Question

我一直在尝试使用 Node JS 和 Sequelize 创建一个聊天应用程序。现在我遇到了一个问题，即创建一个查询以查找具有我的 ID 和用户 ID 的对话(我正在尝试发短信的那个)。所以我想做的事情是发送一个帖子请求和我正在向其发送消息的用户的 id，然后我查看我的对话模型并检查该对话是否有我的 id 和用户的 id我发短信给。
我的模型通过多对多关系关联。所以主要目标是找到与 的对话。仅限 我的 ID 和我用 发短信的用户的 ID相同对话 ID。
这是我的模型:
用户

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define(
    "User",
    {
      name: { type: DataTypes.STRING },
      password: { type: DataTypes.STRING, allowNull: false },
      username: { type: DataTypes.STRING, allowNull: false },
      email: { type: DataTypes.STRING, allowNull: false },
    },
    {}
  );


    User.belongsToMany(models.Conversation, {
      as: "conversations",
      foreignKey: "user_id",
      through: models.ConversationUsers,
    });
    User.hasMany(models.Message, {
      as: "messages",
    });
  };
  return User;
};

对话

module.exports = (sequelize, DataTypes) => {
  const Conversation = sequelize.define(
    "Conversation",
    {
      lastMessage: DataTypes.STRING,
      recipients: DataTypes.ARRAY(DataTypes.INTEGER),
    },
    {
      sequelize,
      modelName: "Conversation",
    }
  );

  Conversation.associate = (models) => {
    Conversation.belongsToMany(models.User, {
      as: "participants",
      foreignKey: "conversation_id",
      through: models.ConversationUsers,
    });
    Conversation.hasMany(models.Message, {
      as: "messages",
    });
  };

  return Conversation;
};

ConversationUsers 多对多通过模型

"use strict";

module.exports = (sequelize, DataTypes) => {
  const ConversationUsers = sequelize.define(
    "ConversationUsers",
    {
      user_id: DataTypes.INTEGER,
      conversation_id: DataTypes.INTEGER,
    },
    {
      sequelize,
      modelName: "ConversationUsers",
    }
  );

  return ConversationUsers;
};

信息

module.exports = (sequelize, DataTypes) => {
  const Message = sequelize.define(
    "Message",
    {
      conversationId: { type: DataTypes.INTEGER, allowNull: false },
      sentTo: DataTypes.INTEGER,
      sentFrom: DataTypes.INTEGER,
      body: { type: DataTypes.STRING, allowNull: false },
    },
    {
      sequelize,
      modelName: "Message",
    }
  );

  Message.associate = (models) => {
    Message.belongsTo(models.User, {
      as: "messageTo",
      foreignKey: "sentTo",
    });
    Message.belongsTo(models.User, {
      as: "messageFrom",
      foreignKey: "sentFrom",
    });
    Message.belongsTo(models.Conversation, {
      as: "messages",
    });
  };
  return Message;
};

Answer 1

我认为您可以从模型中删除一些部分并对其进行一些修改。
消息不需要 sentTo , 他们只需要一个 sentFrom .您可以使用 ConversationUsers表以了解收件人是谁。这也使您可以灵活地与超过 2 个成员进行对话，因为您当前的模型本质上强制消息只能发送给一个用户。
所以让我们先看看模型的变化

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define(
    "User",
    {
      name: { type: DataTypes.STRING },
      password: { type: DataTypes.STRING, allowNull: false },
      username: { type: DataTypes.STRING, allowNull: false },
      email: { type: DataTypes.STRING, allowNull: false },
    },
    {
    // I think moving the associations to other files might make this more clear
    }

  );
  };
  return User;
};

module.exports = (sequelize, DataTypes) => {
  const Conversation = sequelize.define(
    "Conversation",
    {
        // perhaps something like a subject could go here e.g.
        subject: DataTypes.STRING(500),
    },
    {
      sequelize,
      modelName: "Conversation",
    }
  );

  Conversation.associate = (models) => {
    Conversation.hasMany(models.Message, {
      as: "ConversationMessages",
    }); // adds ConversationId onto Message, gives us Conversation.getConversationMessages() etc
    models.Message.belongsTo(Conversation); // create association both ways for convenience methods to find convo from a message 
    models.Message.hasOne(Conversation, {
        as: 'LastMessage', 
        constraints: false, 
        allowNull:true, 
        defaultValue:null
    }); // adds LastMessageId onto Conversation model (you'll have to write code to maintain this value, probably through an afterCreate hook on Message model)

  };

  return Conversation;
};

module.exports = (sequelize, DataTypes) => {
  const Message = sequelize.define(
    "Message",
    {
     id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true, // if you want to do the hook thing i talked about to set LastMessageId, you need to put this in 
    },
      body: { type: DataTypes.STRING, allowNull: false },
    },
    {
      sequelize,
      modelName: "Message",
    }
  );

  Message.associate = (models) => {
     Message.belongsTo(models.User, {as: "sentFromUser"});
  };
  return Message;
};

   // I'm going to rename your many-to-many table "ConversationMembers"
   module.exports = (sequelize, DataTypes) => {
   const ConversationMembers = sequelize.define(
    "ConversationMembers",
    {
    // again, the associations will build these fields for you
    },
    {
      sequelize,
      modelName: "ConversationMembers",
    }
    );

    models.Conversation.belongsToMany(models.User, {
      through: "ConversationMember",
      as: "Members",
    }); // gives us Conversation.getMembers()
    models.User.belongsToMany(models.Conversation, {
      through: "ConversationMember",
      as: "MemberConversations",
    }); // gives us User.getMemberConversations()
    ConversationMember.belongsTo(models.Message, { as: "LastReadMessage" }); // gives us the potential ability to track the last read message for each convo member as ConversationMember.LastReadMessageId, you'll need to set this value manually on read for each user if you care about having it
    models.Conversation.hasMany(ConversationMember);
    models.User.hasMany(ConversationMember);
  
    return ConversationMember;

好的，现在回答您的问题，此时可能会变得更简单。如果您已经知道 ConversationId ，您需要做的就是检查发送消息的人是否是对话的成员。如果是，则在 Messages 中写入一行 table 。消息“发给”谁并不重要——你是在写给对话的成员，而不是给任何个人。

async function canMessageHelper({conversationId, userId }) {
    const convo = await models.Conversation.findOne({
        attributes: ["id"], // whatever attrs you need, probably not many if any
        where: {
            id: conversationId,
        },
        include: [{
            model: models.ConversationMember,
            attributes: ["ConversationId"], // whatever you need if anything
            where: { // this where is critical, it creates an inner join so convo only returns if myUserId is a member of the Conversation
                 UserId: userId 
            }
        }]
    });
    
    if (!convo) {
       return false;
    }
    return convo;

}

async function sendMessage({conversationId, authorUserId, messageText}) {
   const allowMessage = await canMessageHelper({conversationId, userId: authorUserId}); 
   if (!allowMessage) {
      return false; 
   }
   await models.Message.create({sentFromUserId: authorUserId, body: messageText});
}

如果您想尝试此操作，请确保在同步之前从数据库中删除已使用这些名称创建的所有表。
我没有为我提到的钩子(Hook)提供任何代码，但你将拥有开发这些想法的基础。