TypeORM: Selecting DISTINCT Values

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:

• TypeORM: Sort Results by a Relation Column
• TypeORM: Counting Records in One-To-Many Relation
• Aggregation Operations in TypeORM (Sum, Avg, Min, Max, Count)
• TypeORM: Find Rows Where Column Value is IN an Array
• Pagination in TypeORM (Find Options & QueryBuilder)

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