TypeORM QueryRunner Select distinct

Question

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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