I face a problem where I need to sort by table field in a case-insensitive order but without modifying it in a selection.

I have data like this in my table.

I have a model like

import { Entity, PrimaryColumn, Column } from 'typeorm';

@Entity('Song')
export class SongEntity {
  @PrimaryColumn()
  public id: number;

  @Column({
    name: 'Name',
  })
  public name: string;

  @Column({
    name: 'Band',
  })
  public band: string;

  @Column({
    name: 'Genre',
  })
  public genre: string;
}

I want to be able to sort data in a case-insensitive mode but leave data as is. For example, when I sort in by band in ascending mode, using the following code.

songRepository.find({
    order: {
        band: "ASC",
    }
})

I want to receive the following output.

However, I receive the next output, because bands are sorted in a case-sensitive mode.

In plain SQL, it's possible to use via lowering field in order condition https://stackoverflow.com/a/2413436/6625548. However, I don't see any option in docs on how to do it via entity repository in the find condition.

What is the right way to do case-insensitive ordering in typeorm?

CodePudding user response：

Comment as full answer:

TypeORM does not have an option for this.

Similiar to https://stackoverflow.com/a/62897893/6459327, you can use QueryBuilder and integrated function of your database to compare and sort everything lower-case.

const songs = await songRepository.createQueryBuilder('songs').select('songs').orderBy('LOWER(songs.band)', 'ASC').getMany();