CODEWITHSUNDEEP
mysqlnode.jstypeorm
Dharita Chokshi
Dharita Chokshi

Reputation: 1263

TypeORM QueryRunner Select distinct

Below is my SQL query:

Select distinct ports.port_name from orders left join port_master on orders.officeId = ports.portId;

How to write the above SQL using typeorm query runner to select the distinct port name? Below syntax fetches all the ports

await queryRunner.manager.find(Orders, {
  relations: ["ports"],
  where: filter
}).then((result: any) => {
  orders = result;
});

Upvotes: 30

Views: 68876

Answers (4)

Željko Šević
Željko Šević

Reputation: 4384

Another way to do this is by using the query builder and distinct method

await this.createQueryBuilder('entity name')
  .select('entity name.column')
  .distinct(true)
  .getRawMany();

Upvotes: 36

H.T.
H.T.

Reputation: 476

In case you are using Postgresql you can use distinctOn and the query looks like this:

await getRepository(Feed)
      .createQueryBuilder('feed')
      .where({ uploaderId: In([1,2,3,4]) })
      .distinctOn(['feed.uploaderId'])
      .orderBy({ 'feed.uploaderId': 'ASC', 'feed.createdAt': 'DESC' })
      .getMany()

In the above example there is a Feed table which I want to get the rows which Id exists in the given array and also distinct the values by uploaderId and then sort it by createdAt date

Upvotes: 28

Ahmed Mohamed Talaat
Ahmed Mohamed Talaat

Reputation: 39

await this.createQueryBuilder("Entity name")
  .select('DISTINCT ("column")')
  .getRawMany();

Upvotes: 1

Kartik Raja S
Kartik Raja S

Reputation: 728

Instead of using Raw query you may also use the below mentioned query builder

await getManager().createQueryBuilder(orders , "odrs")
.leftJoinAndSelect(ports, "pts", "odrs.officeId = pts.portId")
.select('DISTINCT odrs.port_name', 'port_name')
.orderBy("odrs.port_name", "ASC")
.getRawMany();

Thank You

Upvotes: 5

Related Questions