How to orderby on multiple columns using typeorm

When working with TypeORM for data operations, ordering by multiple columns is a common requirement, which can be achieved by using the order option within the find or createQueryBuilder methods. Below are some specific implementation methods and examples.

Using the find Method

When using the find method, you can directly specify the fields to sort by and their direction (ASC for ascending, DESC for descending) within the order property. For example, consider a User entity where we want to sort users by lastName in ascending order, then by firstName in descending order:

typescript
import { getRepository } from 'typeorm';
import { User } from './entity/User';

async function getUsers() {
    const userRepository = getRepository(User);
    const users = await userRepository.find({
        order: {
            lastName: 'ASC',
            firstName: 'DESC'
        }
    });
    return users;
}

Using the createQueryBuilder Method

Using createQueryBuilder provides greater flexibility, especially in complex queries. Similarly, you can use the orderBy method to specify the columns and direction. For instance, applying the same sorting to the User entity:

typescript
import { getRepository } from 'typeorm';
import { User } from './entity/User';

async function getUsers() {
    const userRepository = getRepository(User);
    const users = await userRepository.createQueryBuilder('user')
        .orderBy('user.lastName', 'ASC')
        .addOrderBy('user.firstName', 'DESC')
        .getMany();
    return users;
}

In this example, orderBy is used to set the first sorting condition, while addOrderBy can add additional sorting conditions. This approach is highly effective for handling multi-column sorting, as you can chain multiple addOrderBy calls to add sorting conditions.

Mixing Relationships and Sorting

When dealing with entities that have relationships, you can also sort on columns from related entities. For example, if the User entity has a related Profile entity, and you want to sort by the age field in Profile, you can do the following:

typescript
const users = await userRepository.createQueryBuilder('user')
    .leftJoinAndSelect('user.profile', 'profile')
    .orderBy('profile.age', 'DESC')
    .getMany();

This allows you not only to sort directly on the User entity's properties but also on the properties of its associated Profile.

Summary

The above are several methods for ordering by multiple columns in TypeORM. Using the find method can quickly implement simple sorting, while createQueryBuilder offers greater flexibility and capabilities for complex queries. In actual development, choose the appropriate method based on different scenarios.