如何在 TypeORM 中使用子查询

数据库 5月前 60

本文是关于在 TypeORM 中使用带有查询构建器的子查询。假设我们有一个名为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
}

下面的代码片段通过使用子查询（在查询构建器链中以subQuery() 方法开始并以getQuery() 方法结束）来查找价格高于平均价格的产品：

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);

为了增加可读性，我们可以重写代码如下：

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);