How to Use Subqueries in TypeORM

Updated: January 30, 2024 By: Snowball Post a comment

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:

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

Related Articles