TypeORM 的高级查询技巧有哪些?包括复杂条件、子查询、窗口函数等

TypeORM 提供了强大的查询功能,掌握高级查询技巧可以让你构建出高效、灵活的数据库查询。本文将详细介绍 TypeORM 的各种高级查询技巧。

复杂条件查询

使用 Brackets 构建复杂条件

typescript
import { Brackets } from 'typeorm';

// 构建复杂的 OR 条件
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.status = :status', { status: 'active' })
  .andWhere(
    new Brackets(qb => {
      qb.where('user.name LIKE :name', { name: '%John%' })
        .orWhere('user.email LIKE :email', { email: '%john%' });
    })
  )
  .getMany();

// 等价 SQL: WHERE user.status = 'active' AND (user.name LIKE '%John%' OR user.email LIKE '%john%')

// 多层嵌套条件
const users = await userRepository
  .createQueryBuilder('user')
  .where(
    new Brackets(qb => {
      qb.where('user.age > :minAge', { minAge: 18 })
        .andWhere(
          new Brackets(qb2 => {
            qb2.where('user.status = :status1', { status1: 'active' })
              .orWhere('user.status = :status2', { status2: 'pending' });
          })
        );
    })
  )
  .getMany();

使用 Raw SQL 条件

typescript
// 使用原始 SQL 表达式
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.age > :age', { age: 18 })
  .andWhere('DATE(user.createdAt) > :date', { date: '2024-01-01' })
  .getMany();

// 使用函数
const users = await userRepository
  .createQueryBuilder('user')
  .where('LOWER(user.name) = LOWER(:name)', { name: 'JOHN' })
  .getMany();

// 使用 CASE WHEN
const users = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
    'CASE WHEN user.age >= 18 THEN :adult ELSE :minor END as ageGroup',
  ])
  .setParameter('adult', 'Adult')
  .setParameter('minor', 'Minor')
  .getRawMany();

子查询

使用 EXISTS 子查询

typescript
// 查询有文章的用户
const users = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb
      .subQuery()
      .select('1')
      .from(Post, 'post')
      .where('post.authorId = user.id')
      .getQuery();
    return `EXISTS ${subQuery}`;
  })
  .getMany();

// 等价 SQL: SELECT * FROM user WHERE EXISTS (SELECT 1 FROM post WHERE post.authorId = user.id)

// 查询没有文章的用户
const users = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb
      .subQuery()
      .select('1')
      .from(Post, 'post')
      .where('post.authorId = user.id')
      .getQuery();
    return `NOT EXISTS ${subQuery}`;
  })
  .getMany();

使用 IN 子查询

typescript
// 查询文章数量大于 5 的用户
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.id IN ' +
    userRepository
      .createQueryBuilder('user2')
      .select('user2.id')
      .from(Post, 'post')
      .where('post.authorId = user2.id')
      .groupBy('user2.id')
      .having('COUNT(post.id) > :count', { count: 5 })
      .getQuery()
  )
  .getMany();

// 等价 SQL: SELECT * FROM user WHERE user.id IN (SELECT user2.id FROM post WHERE post.authorId = user2.id GROUP BY user2.id HAVING COUNT(post.id) > 5)

使用子查询进行计算

typescript
// 查询每个用户的文章数量
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoin(
    (subQuery) => {
      return subQuery
        .select('post.authorId', 'authorId')
        .addSelect('COUNT(*)', 'postCount')
        .from(Post, 'post')
        .groupBy('post.authorId');
    },
    'postStats',
    'postStats.authorId = user.id'
  )
  .addSelect('postStats.postCount', 'postCount')
  .getMany();

// 或者使用子查询表达式
const users = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
    `(
      SELECT COUNT(*)
      FROM post
      WHERE post.authorId = user.id
    ) as postCount`,
  ])
  .getRawMany();

聚合和分组

基本聚合函数

typescript
// 统计用户数量
const count = await userRepository.count();

// 统计活跃用户数量
const activeCount = await userRepository.count({
  where: { status: 'active' }
});

// 计算平均年龄
const result = await userRepository
  .createQueryBuilder('user')
  .select('AVG(user.age)', 'avgAge')
  .getRawOne();

// 计算总和
const result = await userRepository
  .createQueryBuilder('user')
  .select('SUM(user.score)', 'totalScore')
  .getRawOne();

// 最大值和最小值
const result = await userRepository
  .createQueryBuilder('user')
  .select('MAX(user.age)', 'maxAge')
  .addSelect('MIN(user.age)', 'minAge')
  .getRawOne();

GROUP BY 和 HAVING

typescript
// 按状态分组统计用户数量
const result = await userRepository
  .createQueryBuilder('user')
  .select('user.status', 'status')
  .addSelect('COUNT(*)', 'count')
  .groupBy('user.status')
  .getRawMany();

// 按年龄分组统计
const result = await userRepository
  .createQueryBuilder('user')
  .select([
    'CASE ' +
    '  WHEN user.age < 18 THEN :minor ' +
    '  WHEN user.age < 30 THEN :young ' +
    '  WHEN user.age < 50 THEN :middle ' +
    '  ELSE :senior ' +
    'END as ageGroup',
    'COUNT(*) as count',
  ])
  .setParameter('minor', 'Minor')
  .setParameter('young', 'Young')
  .setParameter('middle', 'Middle')
  .setParameter('senior', 'Senior')
  .groupBy('ageGroup')
  .getRawMany();

// 使用 HAVING 过滤分组
const result = await userRepository
  .createQueryBuilder('user')
  .select('user.status', 'status')
  .addSelect('COUNT(*)', 'count')
  .groupBy('user.status')
  .having('COUNT(*) > :minCount', { minCount: 10 })
  .getRawMany();

// 多列分组
const result = await userRepository
  .createQueryBuilder('user')
  .select(['user.status', 'user.city'])
  .addSelect('COUNT(*)', 'count')
  .groupBy('user.status')
  .addGroupBy('user.city')
  .getRawMany();

窗口函数

使用窗口函数

typescript
// ROW_NUMBER() - 为每行分配一个唯一的序号
const users = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
    'user.score',
    'ROW_NUMBER() OVER (ORDER BY user.score DESC) as rank',
  ])
  .getRawMany();

// RANK() - 相同分数的用户获得相同的排名
const users = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
    'user.score',
    'RANK() OVER (ORDER BY user.score DESC) as rank',
  ])
  .getRawMany();

// DENSE_RANK() - 紧密排名,不跳过排名
const users = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
    'user.score',
    'DENSE_RANK() OVER (ORDER BY user.score DESC) as rank',
  ])
  .getRawMany();

// LAG() 和 LEAD() - 访问前一行和后一行的数据
const users = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
    'user.score',
    'LAG(user.score) OVER (ORDER BY user.id) as prevScore',
    'LEAD(user.score) OVER (ORDER BY user.id) as nextScore',
  ])
  .getRawMany();

// SUM() OVER() - 计算累计和
const users = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
    'user.score',
    'SUM(user.score) OVER (ORDER BY user.id) as cumulativeScore',
  ])
  .getRawMany();

连接查询

多表连接

typescript
// 左连接多个表
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post')
  .leftJoinAndSelect('post.comments', 'comment')
  .leftJoinAndSelect('comment.author', 'commentAuthor')
  .where('user.id = :id', { id: 1 })
  .getOne();

// 自连接
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.manager', 'manager')
  .where('user.managerId IS NOT NULL')
  .getMany();

// 条件连接
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoin('user.posts', 'post', 'post.status = :status', { status: 'published' })
  .addSelect(['post.id', 'post.title'])
  .where('user.id = :id', { id: 1 })
  .getOne();

复杂连接查询

typescript
// 查询每个用户的最新文章
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoin(
    (subQuery) => {
      return subQuery
        .select('p1.authorId')
        .addSelect('p1.id', 'postId')
        .addSelect('p1.title', 'postTitle')
        .addSelect('p1.createdAt', 'postCreatedAt')
        .from(Post, 'p1')
        .where(
          `p1.createdAt = (
            SELECT MAX(p2.createdAt)
            FROM post p2
            WHERE p2.authorId = p1.authorId
          )`
        );
    },
    'latestPost',
    'latestPost.authorId = user.id'
  )
  .addSelect('latestPost.postTitle', 'latestPostTitle')
  .addSelect('latestPost.postCreatedAt', 'latestPostCreatedAt')
  .getMany();

排序技巧

自定义排序

typescript
// 使用表达式排序
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('CASE user.status WHEN :active THEN 1 WHEN :pending THEN 2 ELSE 3 END', 'ASC')
  .setParameter('active', 'active')
  .setParameter('pending', 'pending')
  .getMany();

// 使用函数排序
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('LENGTH(user.name)', 'DESC')
  .getMany();

// 多条件排序
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('user.status', 'ASC')
  .addOrderBy('user.createdAt', 'DESC')
  .addOrderBy('user.name', 'ASC')
  .getMany();

// 随机排序(MySQL)
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('RAND()')
  .getMany();

// 随机排序(PostgreSQL)
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('RANDOM()')
  .getMany();

NULL 值排序

typescript
// NULL 值排在最后(MySQL)
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('user.age IS NULL', 'ASC')
  .addOrderBy('user.age', 'ASC')
  .getMany();

// NULL 值排在最前(MySQL)
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('user.age IS NULL', 'DESC')
  .addOrderBy('user.age', 'ASC')
  .getMany();

// 使用 COALESCE 处理 NULL
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('COALESCE(user.age, 0)', 'ASC')
  .getMany();

分页优化

基于游标的分页

typescript
// 使用游标分页提高性能
async function getUsersWithCursor(cursor: number, limit: number = 10) {
  const query = userRepository
    .createQueryBuilder('user')
    .orderBy('user.id', 'ASC')
    .limit(limit + 1); // 多取一条判断是否有下一页

  if (cursor) {
    query.where('user.id > :cursor', { cursor });
  }

  const users = await query.getMany();
  const hasNextPage = users.length > limit;
  
  if (hasNextPage) {
    users.pop(); // 移除多取的一条
  }

  return {
    data: users,
    nextCursor: hasNextPage ? users[users.length - 1].id : null,
    hasNextPage
  };
}

// 使用示例
const page1 = await getUsersWithCursor(null, 10);
const page2 = await getUsersWithCursor(page1.nextCursor, 10);

基于时间戳的分页

typescript
// 使用时间戳分页
async function getUsersByTimestamp(
  lastTimestamp: Date | null,
  limit: number = 10
) {
  const query = userRepository
    .createQueryBuilder('user')
    .orderBy('user.createdAt', 'DESC')
    .limit(limit + 1);

  if (lastTimestamp) {
    query.where('user.createdAt < :timestamp', { timestamp: lastTimestamp });
  }

  const users = await query.getMany();
  const hasMore = users.length > limit;
  
  if (hasMore) {
    users.pop();
  }

  return {
    data: users,
    nextTimestamp: hasMore ? users[users.length - 1].createdAt : null,
    hasMore
  };
}

性能优化技巧

使用索引提示

typescript
// 强制使用特定索引(MySQL)
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.email = :email', { email: 'john@example.com' })
  .setHint('FORCE INDEX (idx_user_email)')
  .getOne();

// 使用索引提示(PostgreSQL)
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.email = :email', { email: 'john@example.com' })
  .setHint('SET enable_seqscan = off')
  .getOne();

避免全表扫描

typescript
// ❌ 不好的做法:可能导致全表扫描
const users = await userRepository
  .createQueryBuilder('user')
  .where('LOWER(user.name) = :name', { name: 'john' })
  .getMany();

// ✅ 好的做法:使用索引友好的查询
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.name = :name', { name: 'John' })
  .getMany();

使用 EXISTS 代替 IN

typescript
// ❌ 不好的做法:使用 IN 可能导致性能问题
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.id IN :ids', { ids: [1, 2, 3, 4, 5] })
  .getMany();

// ✅ 好的做法:使用 EXISTS
const users = await userRepository
  .createQueryBuilder('user')
  .where(qb => {
    const subQuery = qb
      .subQuery()
      .select('1')
      .from(Post, 'post')
      .where('post.authorId = user.id')
      .getQuery();
    return `EXISTS ${subQuery}`;
  })
  .getMany();

查询调试

查看生成的 SQL

typescript
// 获取生成的 SQL
const query = userRepository
  .createQueryBuilder('user')
  .where('user.id = :id', { id: 1 });

const sql = query.getSql();
console.log('Generated SQL:', sql);

// 执行查询
const user = await query.getOne();

// 使用日志记录所有查询
const dataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User],
  logging: true, // 启用日志
  maxQueryExecutionTime: 1000, // 记录超过 1 秒的查询
});

分析查询性能

typescript
// 使用 EXPLAIN 分析查询
async function analyzeQuery(query: string) {
  const result = await dataSource.query(`EXPLAIN ${query}`);
  console.log('Query Analysis:', result);
  
  // 检查是否使用了索引
  const usingIndex = result.some(row => 
    row.type === 'ref' || row.type === 'eq_ref'
  );
  
  if (!usingIndex) {
    console.warn('Query not using index');
  }
}

// 使用示例
const query = userRepository
  .createQueryBuilder('user')
  .where('user.email = :email', { email: 'john@example.com' });

await analyzeQuery(query.getSql());

TypeORM 的高级查询技巧提供了强大而灵活的数据库操作能力,掌握这些技巧可以让你构建出高效、复杂的数据库查询。