What are TypeORM's advanced query techniques? Including complex conditions, subqueries, window functions, etc.

TypeORM provides powerful query capabilities. Mastering advanced query techniques allows you to build efficient and flexible database queries. This article details various advanced query techniques in TypeORM.

Complex Conditional Queries

Using Brackets to Build Complex Conditions

typescript
import { Brackets } from 'typeorm';

// Build complex OR conditions
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();

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

// Multi-level nested conditions
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();

Using Raw SQL Conditions

typescript
// Use raw SQL expressions
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.age > :age', { age: 18 })
  .andWhere('DATE(user.createdAt) > :date', { date: '2024-01-01' })
  .getMany();

// Use functions
const users = await userRepository
  .createQueryBuilder('user')
  .where('LOWER(user.name) = LOWER(:name)', { name: 'JOHN' })
  .getMany();

// Use 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();

Subqueries

Using EXISTS Subquery

typescript
// Query users with posts
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();

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

// Query users without posts
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();

Using IN Subquery

typescript
// Query users with more than 5 posts
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();

// Equivalent 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)

Using Subqueries for Calculations

typescript
// Query post count for each user
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();

// Or use subquery expression
const users = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
    `(
      SELECT COUNT(*)
      FROM post
      WHERE post.authorId = user.id
    ) as postCount`,
  ])
  .getRawMany();

Aggregation and Grouping

Basic Aggregate Functions

typescript
// Count users
const count = await userRepository.count();

// Count active users
const activeCount = await userRepository.count({
  where: { status: 'active' }
});

// Calculate average age
const result = await userRepository
  .createQueryBuilder('user')
  .select('AVG(user.age)', 'avgAge')
  .getRawOne();

// Calculate sum
const result = await userRepository
  .createQueryBuilder('user')
  .select('SUM(user.score)', 'totalScore')
  .getRawOne();

// Max and min values
const result = await userRepository
  .createQueryBuilder('user')
  .select('MAX(user.age)', 'maxAge')
  .addSelect('MIN(user.age)', 'minAge')
  .getRawOne();

GROUP BY and HAVING

typescript
// Count users by status
const result = await userRepository
  .createQueryBuilder('user')
  .select('user.status', 'status')
  .addSelect('COUNT(*)', 'count')
  .groupBy('user.status')
  .getRawMany();

// Count users by age group
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();

// Filter groups using HAVING
const result = await userRepository
  .createQueryBuilder('user')
  .select('user.status', 'status')
  .addSelect('COUNT(*)', 'count')
  .groupBy('user.status')
  .having('COUNT(*) > :minCount', { minCount: 10 })
  .getRawMany();

// Multi-column grouping
const result = await userRepository
  .createQueryBuilder('user')
  .select(['user.status', 'user.city'])
  .addSelect('COUNT(*)', 'count')
  .groupBy('user.status')
  .addGroupBy('user.city')
  .getRawMany();

Window Functions

Using Window Functions

typescript
// ROW_NUMBER() - Assign unique sequential number to each row
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() - Users with same score get same 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() - Dense ranking, no gaps in ranking
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() and LEAD() - Access previous and next row data
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() - Calculate cumulative sum
const users = await userRepository
  .createQueryBuilder('user')
  .select([
    'user.id',
    'user.name',
    'user.score',
    'SUM(user.score) OVER (ORDER BY user.id) as cumulativeScore',
  ])
  .getRawMany();

Join Queries

Multi-table Joins

typescript
// Left join multiple tables
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();

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

// Conditional join
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();

Complex Join Queries

typescript
// Query each user's latest post
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();

Sorting Techniques

Custom Sorting

typescript
// Sort using expressions
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();

// Sort using functions
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('LENGTH(user.name)', 'DESC')
  .getMany();

// Multi-condition sorting
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('user.status', 'ASC')
  .addOrderBy('user.createdAt', 'DESC')
  .addOrderBy('user.name', 'ASC')
  .getMany();

// Random sorting (MySQL)
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('RAND()')
  .getMany();

// Random sorting (PostgreSQL)
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('RANDOM()')
  .getMany();

NULL Value Sorting

typescript
// NULL values last (MySQL)
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('user.age IS NULL', 'ASC')
  .addOrderBy('user.age', 'ASC')
  .getMany();

// NULL values first (MySQL)
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('user.age IS NULL', 'DESC')
  .addOrderBy('user.age', 'ASC')
  .getMany();

// Use COALESCE to handle NULL
const users = await userRepository
  .createQueryBuilder('user')
  .orderBy('COALESCE(user.age, 0)', 'ASC')
  .getMany();

Pagination Optimization

Cursor-based Pagination

typescript
// Use cursor pagination for better performance
async function getUsersWithCursor(cursor: number, limit: number = 10) {
  const query = userRepository
    .createQueryBuilder('user')
    .orderBy('user.id', 'ASC')
    .limit(limit + 1); // Fetch one extra to check if there's a next page

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

  const users = await query.getMany();
  const hasNextPage = users.length > limit;
  
  if (hasNextPage) {
    users.pop(); // Remove the extra one
  }

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

// Usage example
const page1 = await getUsersWithCursor(null, 10);
const page2 = await getUsersWithCursor(page1.nextCursor, 10);

Timestamp-based Pagination

typescript
// Use timestamp pagination
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
  };
}

Performance Optimization Techniques

Using Index Hints

typescript
// Force use of specific index (MySQL)
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.email = :email', { email: 'john@example.com' })
  .setHint('FORCE INDEX (idx_user_email)')
  .getOne();

// Use index hints (PostgreSQL)
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.email = :email', { email: 'john@example.com' })
  .setHint('SET enable_seqscan = off')
  .getOne();

Avoid Full Table Scans

typescript
// ❌ Bad: May cause full table scan
const users = await userRepository
  .createQueryBuilder('user')
  .where('LOWER(user.name) = :name', { name: 'john' })
  .getMany();

// ✅ Good: Use index-friendly queries
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.name = :name', { name: 'John' })
  .getMany();

Use EXISTS Instead of IN

typescript
// ❌ Bad: Using IN may cause performance issues
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.id IN :ids', { ids: [1, 2, 3, 4, 5] })
  .getMany();

// ✅ Good: Use 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();

Query Debugging

View Generated SQL

typescript
// Get generated SQL
const query = userRepository
  .createQueryBuilder('user')
  .where('user.id = :id', { id: 1 });

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

// Execute query
const user = await query.getOne();

// Log all queries
const dataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User],
  logging: true, // Enable logging
  maxQueryExecutionTime: 1000, // Log queries over 1 second
});

Analyze Query Performance

typescript
// Use EXPLAIN to analyze query
async function analyzeQuery(query: string) {
  const result = await dataSource.query(`EXPLAIN ${query}`);
  console.log('Query Analysis:', result);
  
  // Check if index is used
  const usingIndex = result.some(row => 
    row.type === 'ref' || row.type === 'eq_ref'
  );
  
  if (!usingIndex) {
    console.warn('Query not using index');
  }
}

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

await analyzeQuery(query.getSql());

TypeORM's advanced query techniques provide powerful and flexible database operation capabilities. Mastering these techniques allows you to build efficient and complex database queries.