能否在QueryBuilder中新增属性？优化聊天室最后消息查询方案

我定义了存储对话的chat-room实体及对应的chat-room-message实体，代码如下：

chat-room.entity.ts #

@Entity('chats-room')
export class Chatroom {
    @PrimaryGeneratedColumn('uuid')
    chatroomId:string;

    @ManyToOne(() => User,user => user.chatrooms)
    userOne:User

    @ManyToOne(() => User,user => user.chatrooms)
    userTwo:User

    @OneToMany(() => ChatRoomMessage,message => message.chatroom)
    chatRoomMessages:ChatRoomMessage[]
}

chat-room-message.entity.ts #

@Entity('chats-room-message')
export class ChatRoomMessage {
  @PrimaryGeneratedColumn('uuid')
  messageId: string;

  @ManyToOne(() => User, (user) => user.chatRoomMessages)
  user: User;

  @ManyToOne(() => Chatroom, (chatroom) => chatroom.chatRoomMessages)
  chatroom: Chatroom;

  @Column()
  content?: string;

  @CreateDateColumn({ type: 'timestamp with time zone' })
  timestamp: Date;
}

我通过以下QueryBuilder查询所有关联指定用户的chat-room实体：

const chatRoomRepository = this.dataSource.getRepository(Chatroom);
const chatRooms = await chatRoomRepository
  .createQueryBuilder('chats-room')
  .select('chats-room')
  .where('chats-room.userOne.userId=:id', {
    id: user.userId,
  })
  .orWhere('chats-room.userTwo.userId=:id', {
    id: user.userId,
  })
  .leftJoinAndSelect('chats-room.userOne', 'userOne')
  .leftJoinAndSelect('chats-room.userTwo', 'userTwo')
  .getMany();

现在我希望为每个chat-room项添加lastMessage属性，当前方案是遍历chatRoom数组，通过Promise.all执行多次查询获取每个聊天室的最后一条消息：

const chatRoomMessageRepository = this.dataSource.getRepository(ChatRoomMessage);
Promise.all(
  chatRoom.map(
    async (item: any) =>
      (item.lastMessage = await chatRoomMessageRepository.findOne({
        where: {
          chatroom: item,
        },
        order: {
          timestamp: 'DESC',
        },
        relations: {
          chatroom: true,
        },
      })),
  ),
).then((data) => console.log(data));

我认为这种方式会产生过多查询，请问是否有更优的解决方案？同时能否在QueryBuilder中直接创建新属性？

方法一：用子查询一次获取所有聊天室的最新消息 #

通过leftJoinSubQuery在单次SQL查询中完成聊天室列表和对应最新消息的关联，彻底避免N+1查询问题：

const chatRooms = await chatRoomRepository
  .createQueryBuilder('chats-room')
  .select(['chats-room', 'userOne', 'userTwo', 'lastMessage'])
  .leftJoinAndSelect('chats-room.userOne', 'userOne')
  .leftJoinAndSelect('chats-room.userTwo', 'userTwo')
  // 子查询：获取每个聊天室的最新消息时间戳
  .leftJoinSubQuery(
    (qb) => qb
      .select('msg.chatroomId', 'chatroomId')
      .addSelect('MAX(msg.timestamp)', 'maxTimestamp')
      .from(ChatRoomMessage, 'msg')
      .groupBy('msg.chatroomId'),
    'latestTimestamp',
    'latestTimestamp.chatroomId = chats-room.chatroomId'
  )
  // 关联对应时间戳的消息记录
  .leftJoin(ChatRoomMessage, 'lastMessage', `
    lastMessage.chatroomId = chats-room.chatroomId 
    AND lastMessage.timestamp = latestTimestamp.maxTimestamp
  `)
  .where('chats-room.userOne.userId = :id OR chats-room.userTwo.userId = :id', { id: user.userId })
  .getMany();

查询结果中每个Chatroom对象会自动带上lastMessage属性，包含该聊天室的最新消息数据。

方法二：为实体添加自定义属性并映射 #

如果希望更规范地处理，可以先在Chatroom实体中添加非数据库存储的自定义属性：

@Entity('chats-room')
export class Chatroom {
    // 原有属性...

    @OneToMany(() => ChatRoomMessage,message => message.chatroom)
    chatRoomMessages:ChatRoomMessage[]

    // 自定义属性，不映射到数据库
    @Column({ select: false, insert: false, update: false })
    lastMessage?: ChatRoomMessage;
}

然后在QueryBuilder中通过子查询将最新消息映射到该属性：

const chatRooms = await chatRoomRepository
  .createQueryBuilder('chats-room')
  .select(['chats-room', 'userOne', 'userTwo', 'lastMessage'])
  .leftJoinAndSelect('chats-room.userOne', 'userOne')
  .leftJoinAndSelect('chats-room.userTwo', 'userTwo')
  .leftJoinSubQuery(
    (qb) => qb
      .select('msg.chatroomId', 'chatroomId')
      .addSelect('msg.messageId', 'messageId')
      .addSelect('msg.content', 'content')
      .addSelect('msg.timestamp', 'timestamp')
      .addSelect('msg.userId', 'userId')
      .from(ChatRoomMessage, 'msg')
      .orderBy('msg.timestamp', 'DESC')
      .groupBy('msg.chatroomId'),
    'lastMessage',
    'lastMessage.chatroomId = chats-room.chatroomId'
  )
  .where('chats-room.userOne.userId = :id OR chats-room.userTwo.userId = :id', { id: user.userId })
  .getMany();

核心优化点 #

• 消除N+1查询：将多次单条消息查询合并为一次数据库请求，大幅提升性能
• 直接在QueryBuilder中通过子查询关联数据，为结果添加lastMessage属性
• 利用SQL聚合能力在数据库层面筛选最新消息，避免前端遍历处理

内容的提问来源于stack exchange，提问作者Trần Phước Lộc