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

Time:08-24

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?

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