如何编写等效于指定SQL语句的Mongoose查询并实现双向聊天记录的格式化展示

Got it, let's break this down step by step—first translating your SQL query to Mongoose, then formatting the results to match your desired output.

Step 1: Equivalent Mongoose Query #

Your SQL statement targets chats between user 1 and user 2 (in either direction). In Mongoose, we use the $or operator to wrap the two sender-receiver pairs, which mirrors the OR logic in your SQL:

// First, define your model from the schema
const Chat = mongoose.model('Chat', chatSchema);

// The query that matches your SQL
const chatQuery = Chat.find({
  $or: [
    { sender_id: 1, receiver_id: 2 },
    { sender_id: 2, receiver_id: 1 }
  ]
});

This will fetch exactly the same documents as your SQL query—all messages where user 1 sent to user 2, or user 2 sent to user 1.

Step 2: Formatting the Results #

Once you retrieve the chat documents, you can map over them to convert each entry into the userX : "message" format. Here are two common implementation approaches:

Using Async/Await (Modern, Clean Syntax)

async function fetchAndFormatChats() {
  try {
    // Execute the query and get results
    const chats = await chatQuery;

    // Transform each chat document to the desired string format
    const formattedChats = chats.map(chat => {
      const userName = chat.sender_id === 1 ? 'user1' : 'user2';
      return `${userName} : "${chat.chattext}"`;
    });

    // Print the formatted output
    console.log(formattedChats.join('\n'));
  } catch (error) {
    console.error('Failed to fetch chats:', error);
  }
}

// Run the function
fetchAndFormatChats();

Using Promises

If you prefer promise chaining instead of async/await:

chatQuery
  .then(chats => {
    const formattedChats = chats.map(chat => {
      const userName = chat.sender_id === 1 ? 'user1' : 'user2';
      return `${userName} : "${chat.chattext}"`;
    });
    console.log(formattedChats.join('\n'));
  })
  .catch(error => console.error('Failed to fetch chats:', error));

Sample Output #

With your example data, both implementations will output:

user1 : "hi"
user2 : "hello"
user1 : "how are you?"

Just ensure you've connected to your MongoDB instance before running the query, and your model is properly initialized with the schema you provided.

内容的提问来源于stack exchange，提问作者Programmer Bengkulu