How to make case-insensitive sorting by field without changing original data from selection

Question

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.

Song	Band	Genre
Event Horizon	Wildways	Metalcore
Doppelganger	palach	Rap
Gone With the Wind	Architects	Metalcore
Chelsea's Smile	Bring Me the Horizon	Death core
1984	Louna	Alternative rock
Bye-bye kitty	playingtheangel	Rap
Deliverance	DSHMC	Metalcore
Anyway	Magnum	Rap
crybaby	aikko	Rap

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.

Band
aikko
Architects
Bring Me the Horizon
DSHMC
Louna
Magnum
palach
playingtheangel
Wildways

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

Band
Architects
Bring Me the Horizon
DSHMC
Louna
Magnum
Wildways
aikko
palach
playingtheangel

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?

Answer 1

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