CODEWITHSUNDEEP
javascriptpostgresqltypeorm
alexr89
alexr89

Reputation: 410

Using Typeorm to return ROW_NUMBER() from a Postgres database

Im struggling to work out how to use the ROW_NUMBER() function with Typorm and Postgres to return the row number dynamically for an ordered query. Information on this can be found here.

One thing to point out is that I have worked out how to do this in Typeorm, using getRawMany() (explained below) however it seems wrong to create an entity and then not return the fields of the entity (using getMany()).

I have the following entity:

@ObjectType()
@Entity()
export class Photo extends BaseEntity {
  @Field(() => ID)
  @PrimaryGeneratedColumn()
  id: number

  @Field()
  @Column('text')
  name: string

  @Field()
  @Column('int', { default: 0 })
  like_count: number
}

I want to use the row_number() function to "rank" the photos based on their position in an ordered query. For example, ordering by like_count will return row_number 1 for the first, 2 for the second and so on.

The query for this is as follows:

select *, ROW_NUMBER () OVER (ORDER BY like_count desc) from "photo" p

Running this query directly on the table returns the row_number, as expected:

| id| name | like_count | row_number
| --| ---- | -----------| ----------|
| 3 | Zebra| 29         | 1         |
| 1 | Dog  | 23         | 2         |
| 2 | Cat  | 9          | 3         |

However, when using the Typeorm createQueryBuilder, no row_number is returned:

await getConnection()
      .createQueryBuilder()
      .select('photo')
      .addSelect('ROW_NUMBER () OVER (ORDER BY "like_count" DESC)')
      .from(Photo, 'photo')
      .getMany()

| id| name | like_count |
| --| ---- | -----------|
| 3 | Zebra| 29         |
| 1 | Dog  | 23         |
| 2 | Cat  | 9          |

Just to reiterate again: if I use getRawMany() here instead of getMany(), then row_number is returned. Therefore, I believe my createQueryBuilder is syntactically correct. It just seems like a "hacky" wrong solution to use getRawMany here.

Does anyone know of a solution to return row_number in a less hacky way? My thought is that I need to have a field on my Photo entity, but I dont think there is any Typeorm field/column/generated decorator for the value of the PostgreSQL ROW_NUMBER Function, nor do I think there is a Typeorm Function I can chain from the createQueryBuilder for this?

Upvotes: 6

Views: 4404

Answers (1)

Sandesh Bhoir
Sandesh Bhoir

Reputation: 51

You can try .getRawMany() instead

Upvotes: 2

Related Questions