使用TypeORM从PostgreSQL随机选N条数据时遇DISTINCT报错

在NestJS中使用TypeORM 0.3.11从PostgreSQL数据库选取指定数量的Question数据时，调用自定义查询方法持续抛出异常：

QueryFailedError: for SELECT DISTINCT, ORDER BY expressions must appear in select list

编写的查询方法如下，预期实现：筛选对应quiz ID的记录、关联加载answers、随机打乱问题并仅取20条：

async findByQuizAndServeRandomlyQuery(quiz: Quiz): Promise<Question[]> {
  const questions = await this.questionRepository
    .createQueryBuilder()
    .setFindOptions({
      where: {
        quiz: {
          id: quiz.id,
        },
      },
      relations: {
        answers: true,
      },
    })
    .orderBy('RANDOM()')
    .take(20)
    .getMany();

  return questions;
}

已确认quiz变量已定义，PostgreSQL支持RANDOM()函数，但仍触发上述异常，且不清楚查询中为何会包含DISTINCT。

补充Question实体定义：

import { Exclude, Expose } from 'class-transformer';
import {
  Column,
  Entity,
  JoinColumn,
  ManyToOne,
  PrimaryGeneratedColumn,
} from 'typeorm';
import { Quiz } from './quiz.entity';

@Entity('quiz-questions')
export class Question {
  constructor(partial?: Partial<Question>) {
    Object.assign(this, partial);

    if (!this.positivePoints) this.positivePoints = 1;
    if (!this.negativePoints) this.negativePoints = 0;
  }

  @Expose()
  @PrimaryGeneratedColumn()
  id: number;

  @Exclude()
  @ManyToOne(() => Quiz, (quiz) => quiz.questions)
  @JoinColumn()
  quiz: Quiz;

  @Expose()
  @Column()
  text: string;

  @Expose()
  @Column({ default: 1 })
  positivePoints: number;

  @Expose()
  @Column({ nullable: true })
  negativePoints: number;
}

问题原因 #

TypeORM在使用relations关联加载关联实体时，会自动添加DISTINCT关键字避免返回重复的主实体记录（关联查询会产生笛卡尔积）。此时用RANDOM()排序，但RANDOM()不在SELECT列表中，违反了PostgreSQL的SQL规则：使用DISTINCT时，ORDER BY的表达式必须出现在SELECT列表里。

解决方法 #

方法一：子查询先选随机ID，再关联加载答案 #

先在子查询中完成随机筛选，再关联查询答案，避免DISTINCT和排序冲突：

import { In } from 'typeorm';

async findByQuizAndServeRandomlyQuery(quiz: Quiz): Promise<Question[]> {
  // 子查询获取随机20个问题ID
  const randomQuestionIds = await this.questionRepository
    .createQueryBuilder('q')
    .select('q.id')
    .where('q.quizId = :quizId', { quizId: quiz.id })
    .orderBy('RANDOM()')
    .take(20)
    .getRawMany();

  // 根据ID查询完整问题及关联答案
  const questionIds = randomQuestionIds.map(item => item.q_id);
  return this.questionRepository.find({
    where: { id: In(questionIds) },
    relations: { answers: true },
  });
}

方法二：显式添加排序表达式到SELECT列表 #

直接用leftJoinAndSelect控制关联查询，同时将RANDOM()加入SELECT列表并别名化，解决规则冲突：

async findByQuizAndServeRandomlyQuery(quiz: Quiz): Promise<Question[]> {
  return this.questionRepository
    .createQueryBuilder('question')
    .leftJoinAndSelect('question.answers', 'answers')
    .where('question.quizId = :quizId', { quizId: quiz.id })
    .addSelect('RANDOM()', 'random_order')
    .orderBy('random_order')
    .take(20)
    .getMany();
}

方法三：禁用自动DISTINCT（不推荐） #

通过distinct(false)关闭TypeORM自动添加的DISTINCT，但可能返回重复的Question记录（关联查询的笛卡尔积导致），需谨慎使用：

async findByQuizAndServeRandomlyQuery(quiz: Quiz): Promise<Question[]> {
  return this.questionRepository
    .createQueryBuilder()
    .setFindOptions({
      where: { quiz: { id: quiz.id } },
      relations: { answers: true },
    })
    .distinct(false)
    .orderBy('RANDOM()')
    .take(20)
    .getMany();
}

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