TypeORM: Selecting DISTINCT Values

Updated: September 4, 2022 By: Pennywise Post a comment

In real life, it’s possible that a column contains many duplicate values. However, there might be occasions when you only want to get different (unique) values. If you’ve worked with MySQL or PostgreSQL, you are very likely to be familiar with the SELECT DISTINCT statement. How can you do that with TypeORM? The example below will make things clear.

The Example

Assume that we have a table named products that contains information about the names, prices, and colors of the goods. Our goal is to list all distinct prices and colors. Here’s how to do it:

const productRepository = myDataSource.getRepository(Product);

// select unique prices
const uniquePrices = await productRepository
      .createQueryBuilder('product')
      .select('DISTINCT product.price', 'price')
      .getRawMany();

console.log(uniquePrices);

// select unique colors
const uniqueColors = await productRepository
      .createQueryBuilder('product')
      .select('product.color', 'color')
      .distinct(true)
      .getRawMany();

console.log(uniqueColors);

You may notice a slight difference between the two query builders. The first one adds DISTINCT right in the select section while the second one adds distinct(true).

Here’s the Product entity for your preference:

// Product entity
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
} from 'typeorm';

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

  @Column()
  name: string;

  @Column()
  color: string;

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

That’s it. Further reading: