TypeORM: Sort Results by a Relation Column

Last updated on September 5, 2022 Pennywise Loading... Post a comment

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:

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

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

Related Articles