How to Use Subqueries in TypeORM

This article is about using subqueries with query builders in TypeORM. Let’s say we have an entity named Product:

// KindaCode.com - Example
// Product entity
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
} from 'typeorm';

@Entity({name: 'products'})
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column({default: 0})
  price: number
}

The code snippet below finds products with a higher than average price by using a subquery (that starts with the subQuery() method and ends with the getQuery() method in the query builder chaining):

const productRepository = myDataSource.getRepository(Product);
const myQueryBuilder = productRepository.createQueryBuilder('product');

// Fetch products with price greater than average price
const products = await myQueryBuilder
      .select()
      .where(
        `product.price > ${myQueryBuilder
          .subQuery()
          .select('AVG(price)')
          .from(Product, 'product')
          .getQuery()}`
      )
      .getMany();

console.log(products);

To increase readability, we can rewrite the code as follows:

const productRepository = myDataSource.getRepository(Product);
const myQueryBuilder = productRepository.createQueryBuilder('product');
const subQuery = myQueryBuilder
      .subQuery()
      .select('AVG(product.price)', 'avgPrice')
      .from(Product, 'product')
      .getQuery();

// Fetch products with price greater than average price
const products = await myQueryBuilder
      .select()
      .where(`product.price > (${subQuery})`)
      .getMany();

console.log(products);

The example above is quite simple and mediocre but from here, you’re pretty good to go. Note that in TypeORM, you can use subqueries with FROM, WHERE, and JOIN.

Further reading:

• TypeORM: Get Raw SQL Query from QueryBuilder
• TypeORM: Selecting DISTINCT Values
• TypeORM: Counting Records in One-To-Many Relation
• How to Store JSON Object with TypeORM
• TypeORM: How to Limit Query Execution Time

You can also check out our database topic page for the latest tutorials and examples.