如何在NestJS+TypeORM中按用户最后发送消息时间排序用户

基于NestJS、TypeORM和PostgreSQL开发应用时，需获取按用户最后发送消息时间戳排序的用户列表。已定义User和Message实体，数据表记录及期望结果如下，尝试的查询代码报错，寻求正确实现方案。

实体定义 #

user.entity.ts #

import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from 'typeorm';
import { Message } from './message.entity';

@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @OneToMany(() => Message, (message) => message.user)
  messages: Message[];
}

message.entity.ts #

import { Entity, PrimaryGeneratedColumn, Column, ManyToOne, CreateDateColumn } from 'typeorm';
import { User } from './user.entity';

@Entity('messages')
export class Message {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  userId: number;

  @Column()
  message: string;

  @CreateDateColumn({ type: 'timestamp' })
  created: Date;

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

数据表记录 #

Users表 #

Messages表 #

期望结果 #

[
  {
    "id": 3,
    "name": "Norman"
  },
  {
    "id": 2,
    "name": "Mike"
  },
  {
    "id": 1,
    "name": "John"
  }
]

尝试的查询及报错 #

this.userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.messages', 'message')
  .groupBy('user.id')
  .addGroupBy('user.name')
  .orderBy('MAX(message.created)', 'DESC')
  .getMany();

报错信息：

"MAX(message" alias was not found. Maybe you forgot to join it?

方案1：子查询获取用户最后消息时间（性能更优） #

无需关联所有消息记录，适合数据量较大的场景：

this.userRepository
  .createQueryBuilder('user')
  .leftJoin(
    (qb) => qb
      .select('userId', 'userId')
      .addSelect('MAX(created)', 'lastMessageTime')
      .from(Message, 'message')
      .groupBy('userId'),
    'userLastMessage',
    'user.id = userLastMessage.userId'
  )
  .orderBy('userLastMessage.lastMessageTime', 'DESC', 'NULLS LAST')
  .select(['user.id', 'user.name'])
  .getMany();

NULLS LAST配置确保无消息的用户排在末尾，匹配示例中John的排序位置。

方案2：主查询添加MAX字段别名 #

若需同时获取用户最后消息时间，可采用此方式：

this.userRepository
  .createQueryBuilder('user')
  .leftJoin('user.messages', 'message')
  .groupBy('user.id')
  .addGroupBy('user.name')
  .addSelect('MAX(message.created)', 'lastMessageTime')
  .orderBy('lastMessageTime', 'DESC', 'NULLS LAST')
  .select(['user.id', 'user.name'])
  .getMany();

通过addSelect为聚合结果设置别名，避免TypeORM解析MAX(message.created)时出现错误。

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