TypeORM: Sort Results by a Relation Column

When using TypeORM to interact with databases, there might be occasions when you want to take records and sort them based on a relational field. There are 2 solutions for this: using find options and using a query builder. Let’s see the example below for more clarity.

The Example

In this example, we have 2 entities: User and Post. The relationship between them is One-To-Many/Many-To-One. Our goal is to retrieve all posts in ascending order of user age ((or descending order, if you prefer).

Defining Entities

User entity:

// KindaCode.com
// User entity
import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from 'typeorm';
import { Post } from './Post';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column({nullable: true})
  age: number;

  @OneToMany(type => Post, post => post.user)
  posts: Post[];
}

Post entity:

// KindaCode.com Example
// Post entity
import { Entity, Column, PrimaryGeneratedColumn, ManyToOne } from 'typeorm';
import { User } from './User';

@Entity()
export class Post {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne((type) => User, (user) => user.posts, { cascade: true })
  user: User;

  @Column()
  title: string;

  @Column()
  body: string;
}

Using Find Options

This approach works for TypeORM 0.3 and newer. For older versions, consider using a query builder instead.

With the code snippet below, posts by younger users will appear first:

const postRepository = myDataSource.getRepository(Post);

const posts = await postRepository.find({
      relations: {
        user: true,
      },
      order: {
        user: {
          age: 'ASC',
        },
      },
});

console.log(posts);

Using Query Builder

This code does the something as the previous snippet:

const postRepository = myDataSource.getRepository(Post);

const posts = await postRepository
      .createQueryBuilder('post')
      .leftJoinAndSelect('post.user', 'user')
      .orderBy('user.age', 'ASC')
      .getMany();

console.log(posts);

Conclusion

We’ve walked through a couple of different ways to get data from the database in order of a relation column. This knowledge is helpful when you have to deal with complex databases with relationships between tables. If you’d like to learn more new and exciting things about TypeORM and other backend technologies, take a look at the following articles:

• Aggregation Operations in TypeORM (Sum, Avg, Min, Max, Count)
• TypeORM: Using LIKE Operator (2 Examples)
• TypeORM: How to store BIGINT correctly
• TypeORM: 2 Ways to Exclude a Column from being Selected
• PostgreSQL: How to Rename a Column of a Table
• 2 Ways to View the Structure of a Table in PostgreSQL

You can also check out our database topic page for the latest tutorials and examples.