TypeORM: How to order by a relation field

Question

I have a Singer entity and a related Song entity

Singer entity

export class Singer {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @OneToMany( type => Song, Song => Song.user )
    songs: Song[];

}

Song entity

export class Song {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @ManyToOne( type => Singer, Singer => Singer.songs )
    singer: Singer;

}

I want to get all Songs ordered by Singer name

I searched the docs and GitHub issues but can't find an answer How can I solve this? better without QueryBuilder

Answer 1

I don't think it is currently supported by typeorm without the query builder, there is currently a feature request open

With the QueryBuilder it is quite simple though:

connection.createQueryBuilder(Song, 'songs')
   .leftJoinAndSelect('songs.singer', 'singer')
   .orderBy('singer.name', 'ASC')
   .getMany();

Answer 2

I had the same issue i tried to order by custom column but without query builder because i was using Repository, this is my solution :

let res = await this.trackingRepository.findAndCount({
      where: [{ username : Like(`%${searchValue}%`) },
              { action : Like(`%${searchValue}%`) },
              { ip : Like(`%${searchValue}%`) }],
      order: {
        [sortField]: sortOrder === "descend" ? 'DESC' : 'ASC',
      },
      skip: (current - 1) * pageSize,
      take: pageSize,
    });

Answer 3

Try the default order on the entity model

https://github.com/typeorm/typeorm/blob/master/sample/sample30-default-order-by/entity/Post.ts

@Entity("sample30_post", {
    orderBy: {
        title: "ASC",
        id: "DESC"
    }
})
export class Post {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    title: string;

}

Answer 4

You do not have to use the query builder if you are willing to do the ordering in-memory. (Careful: This implies fetching all data sets first and then conduct the filtering on your node server).

For this, you can delegate the task to a library like lodash. This way, you could still use the EntityManager or a Repository to query the data.

// first fetch the song and include (=join) the
// singer by the foreign key "singer"
var queryResult = await this.entityManager.find(Song, {
  relations: ['singer'],
});

// then use a library like lodash to do the ordering
const songsSortedBySinger = _.orderBy(queryResult, song => song.singer.name);

For further reading:

• The relations find option is documented here.
• lodash's orderBy is documented here.

Answer 5

This is supported as of TypeORM 0.3.0:

songRepository.find({
    order: {
        singer: {
            name: "ASC"
        }
    }
})

Answer 6

You could do it with a workaround with entity hooks if the result is not too large:

  @AfterLoad()
  sortItems() {
    if (this?.sortableItems?.length) {
      this.sortableItems.sort((a, b) => a.weight - b.weight);
    }
  }

Answer 7

I think typeorm added support for this feature. In my codebase I use a syntax like this, and it orders the results based on the singer.name.

const queryResult = await this.songRepository.find(Song, {
  relations: ['singer'],
  order: {
        'singer.name': 'ASC',
      },
});