TypeORM QueryBuilder 如何写复杂查询？

QueryBuilder 适合处理 Repository 的 find 选项不太好表达的查询，比如多表关联、复杂条件组合、聚合统计、子查询、批量更新删除。它的核心价值不是“写法更高级”，而是让你在 TypeScript 里拼出可控的 SQL，同时继续使用参数绑定、实体映射和事务能力。

如果只是按主键查一条数据，用 repository.findOne() 就够了；如果查询里开始出现 JOIN、GROUP BY、HAVING、EXISTS 或动态条件，QueryBuilder 会更清晰。

如何创建 QueryBuilder

最常用的写法是从 Repository 创建，这样 TypeORM 已经知道主表实体，只需要给它一个别名。

typescript
const userRepository = dataSource.getRepository(User);

const qb = userRepository.createQueryBuilder('user');

const users = await qb.getMany();

也可以从 DataSource 创建，但要把实体和别名都传进去：

typescript
const users = await dataSource
  .createQueryBuilder(User, 'user')
  .where('user.isActive = :isActive', { isActive: true })
  .getMany();

还有一种更接近 SQL 的写法，适合原始表或临时构造查询：

typescript
const users = await dataSource
  .createQueryBuilder()
  .select('user')
  .from(User, 'user')
  .getMany();

不要写成 dataSource.createQueryBuilder('user') 后直接查实体。单独传字符串别名并不能告诉 TypeORM 主表是谁，实际项目里容易生成错误 SQL 或拿不到实体映射。

条件查询怎么写

where 会设置第一段条件，andWhere 和 orWhere 继续追加。参数用 :name 占位，再通过对象传值。

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('user.age >= :minAge', { minAge: 18 })
  .andWhere('user.isActive = :isActive', { isActive: true })
  .getMany();

如果条件里有 OR，建议用 Brackets 明确括号范围。否则 SQL 的 AND / OR 优先级可能和你想的不一样。

typescript
import { Brackets } from 'typeorm';

const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('user.isActive = :isActive', { isActive: true })
  .andWhere(
    new Brackets(qb => {
      qb.where('user.role = :adminRole', { adminRole: 'admin' })
        .orWhere('user.score >= :minScore', { minScore: 90 });
    })
  )
  .getMany();

上面生成的逻辑接近：

sql
user.isActive = true AND (user.role = 'admin' OR user.score >= 90)

参数绑定和 SQL 注入安全

QueryBuilder 可以写 SQL 片段，但不要把用户输入直接拼进字符串。

错误写法：

typescript
.where(`user.name = '${keyword}'`)

正确写法：

typescript
.where('user.name = :name', { name: keyword })

模糊查询也一样，通配符放在参数值里：

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('user.name LIKE :keyword', { keyword: `%${keyword}%` })
  .getMany();

IN 查询要用 TypeORM 的数组展开语法 :...ids：

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('user.id IN (:...ids)', { ids: [1, 2, 3] })
  .getMany();

不要写成 user.id IN :ids，多数数据库不会把它解析成合法的 IN (...)。也不要把 Like()、Between()、In()、MoreThan() 这些 FindOptions 操作符直接塞进字符串条件里；在 QueryBuilder 的字符串条件中，应当使用 SQL 操作符：

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('user.age BETWEEN :start AND :end', { start: 18, end: 30 })
  .andWhere('user.score > :score', { score: 60 })
  .getMany();

关联查询：leftJoin、innerJoin 和 addSelect

leftJoinAndSelect 会做左连接，并把关联实体一起映射回来。即使用户没有文章，用户也会出现在结果中。

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post')
  .where('user.id = :id', { id: 1 })
  .getMany();

innerJoinAndSelect 只返回有关联数据的记录。比如只想查“至少发过文章的用户”，用 inner join 更准确。

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .innerJoinAndSelect('user.posts', 'post')
  .getMany();

如果你只需要关联表的少数字段，可以用 leftJoin 加 addSelect，避免把整张关联表都查出来。

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .leftJoin('user.posts', 'post', 'post.status = :status', { status: 'published' })
  .addSelect(['post.id', 'post.title', 'post.createdAt'])
  .getMany();

多层关联也可以继续往下 join：

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post')
  .leftJoinAndSelect('post.comments', 'comment')
  .leftJoinAndSelect('comment.author', 'commentAuthor')
  .getMany();

多层 join 很方便，但也容易把结果集放大。列表页通常不建议一次性把评论、评论作者、点赞等全部查出来，先把主列表查准，再按业务补必要数据，往往更稳。

分页、排序和 getManyAndCount

分页一般配合稳定排序使用。只写 skip / take 不写 orderBy，翻页时可能出现重复或漏数据。

typescript
const page = 1;
const pageSize = 20;

const [users, total] = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('user.isActive = :isActive', { isActive: true })
  .orderBy('user.createdAt', 'DESC')
  .addOrderBy('user.id', 'DESC')
  .skip((page - 1) * pageSize)
  .take(pageSize)
  .getManyAndCount();

getManyAndCount() 会返回 [列表, 总数]，适合后台管理页或普通列表页。数据量很大时，深分页的 OFFSET 会越来越慢，这时可以考虑基于游标的分页，比如用 createdAt + id 作为翻页条件。

排序可以用单字段，也可以追加多个字段：

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .orderBy('user.createdAt', 'DESC')
  .addOrderBy('user.name', 'ASC')
  .getMany();

随机排序要谨慎。MySQL 的 RAND()、PostgreSQL 的 RANDOM() 在大表上通常很慢，不适合高频接口。

聚合、分组和 Having

统计类查询通常返回原始结果，用 getRawMany() 或 getRawOne() 更合适。

typescript
const rows = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .select('user.role', 'role')
  .addSelect('COUNT(user.id)', 'count')
  .groupBy('user.role')
  .getRawMany();

如果要过滤分组结果，用 having，不是 where：

typescript
const rows = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .select('user.role', 'role')
  .addSelect('COUNT(user.id)', 'count')
  .groupBy('user.role')
  .having('COUNT(user.id) >= :minCount', { minCount: 5 })
  .getRawMany();

常见聚合函数也可以直接写：

typescript
const stats = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .select('COUNT(user.id)', 'count')
  .addSelect('AVG(user.age)', 'avgAge')
  .addSelect('MAX(user.score)', 'maxScore')
  .getRawOne();

注意，聚合结果不是实体字段，返回值一般是字符串或数据库驱动决定的类型。金额、计数这类字段最好在业务层做一次显式转换。

子查询和 EXISTS

子查询适合表达“满足另一张表里的某个条件”。例如查找发过 TypeORM 相关文章的用户：

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb
      .subQuery()
      .select('post.authorId')
      .from(Post, 'post')
      .where('post.title LIKE :title')
      .getQuery();

    return `user.id IN ${subQuery}`;
  })
  .setParameter('title', '%TypeORM%')
  .getMany();

EXISTS 更适合只关心“是否存在”的场景：

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb
      .subQuery()
      .select('1')
      .from(Post, 'post')
      .where('post.authorId = user.id')
      .andWhere('post.status = :status')
      .getQuery();

    return `EXISTS ${subQuery}`;
  })
  .setParameter('status', 'published')
  .getMany();

子查询里也要继续使用参数绑定，不要为了拼 SQL 省掉这一步。

更新和删除

QueryBuilder 不只能查，也能做批量更新和删除。更新时常见写法如下：

typescript
await dataSource
  .createQueryBuilder()
  .update(User)
  .set({ isActive: false })
  .where('lastLoginAt < :date', { date: new Date('2024-01-01') })
  .execute();

如果要基于原字段计算新值，可以用函数形式：

typescript
await dataSource
  .createQueryBuilder()
  .update(User)
  .set({ score: () => 'score + 10' })
  .where('id IN (:...ids)', { ids: [1, 2, 3] })
  .execute();

删除也类似：

typescript
await dataSource
  .createQueryBuilder()
  .delete()
  .from(User)
  .where('createdAt < :date', { date: new Date('2023-01-01') })
  .andWhere('isActive = :isActive', { isActive: false })
  .execute();

批量更新和删除不会像 save() 那样逐个加载实体，也不适合依赖实体生命周期钩子的逻辑。涉及重要数据时，先用同样的条件跑一遍 select 确认范围，再执行写操作。

原生 SQL 和数据库函数

QueryBuilder 可以混合数据库函数，例如 MySQL 的 JSON 查询：

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('JSON_CONTAINS(user.preferences, :preferences)', {
    preferences: JSON.stringify({ theme: 'dark' }),
  })
  .getMany();

如果整段 SQL 都不适合用 QueryBuilder 表达，也可以使用 query 执行原生 SQL：

typescript
const rows = await dataSource.query(
  'SELECT id, name FROM user WHERE createdAt >= ? LIMIT ?',
  [new Date('2024-01-01'), 20]
);

原生 SQL 依然要传参数数组。直接拼用户输入，风险和手写 SQL 完全一样。

缓存怎么用

TypeORM 支持查询缓存，适合不频繁变化、允许短时间延迟的数据，比如字典表、热门配置、低频统计。

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('user.isActive = :isActive', { isActive: true })
  .cache(60_000)
  .getMany();

也可以给缓存指定 id，方便后续清理：

typescript
const users = await dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .where('user.role = :role', { role: 'admin' })
  .cache('admin_users', 60_000)
  .getMany();

缓存不是性能问题的万能药。条件不稳定、权限敏感、更新频繁的数据，不适合随手加缓存。

事务里使用 QueryBuilder

在事务中，要使用事务回调传入的 transactionalEntityManager，不要在中途又切回全局 dataSource。

typescript
await dataSource.transaction(async manager => {
  await manager
    .createQueryBuilder()
    .insert()
    .into(User)
    .values({ name: 'John', email: 'john@example.com' })
    .execute();

  await manager
    .createQueryBuilder()
    .insert()
    .into(Post)
    .values({ title: 'New Post', authorId: 1 })
    .execute();
});

如果需要更细粒度控制，也可以用 QueryRunner 手动开启事务，但要记得释放连接：

typescript
const queryRunner = dataSource.createQueryRunner();

await queryRunner.connect();
await queryRunner.startTransaction();

try {
  await queryRunner.manager
    .createQueryBuilder()
    .update(User)
    .set({ isActive: true })
    .where('id = :id', { id: 1 })
    .execute();

  await queryRunner.commitTransaction();
} catch (error) {
  await queryRunner.rollbackTransaction();
  throw error;
} finally {
  await queryRunner.release();
}

性能优化要看生成的 SQL

QueryBuilder 写起来像链式 API，最终执行的还是 SQL。排查性能问题时，先看它生成了什么。

typescript
const qb = dataSource
  .getRepository(User)
  .createQueryBuilder('user')
  .leftJoin('user.posts', 'post')
  .where('user.isActive = :isActive', { isActive: true });

console.log(qb.getSql());
console.log(qb.getParameters());

常见优化点有这些：

• 避免 N+1 查询：列表中需要关联数据时，用 leftJoinAndSelect 或分批查询，不要在循环里一条条查。
• 只查必要字段：列表页不要默认取大文本、JSON、头像原图这类字段。
• 给过滤和排序字段建索引：where、join、orderBy 里的高频字段尤其要关注。
• 控制 join 数量：多层关联会放大结果集，必要时拆成两次查询。
• 分页要稳定排序：createdAt 后面追加 id，可以减少同一时间数据导致的翻页抖动。
• 用 getRawMany 处理统计结果：聚合统计没必要强行映射成实体。

一个实用判断是：当 QueryBuilder 链条长到你自己都看不清时，不一定要继续硬拼。可以把动态条件封装成函数，把统计查询拆出去，或者直接写一段参数化原生 SQL。ORM 是帮你少写重复代码的，不是让 SQL 从项目里消失的。