How to do orderBy on custom field in typeorm?

When working with TypeORM for data operations, performing orderBy on custom fields is a common requirement, especially when dealing with complex queries or sorting based on calculated results from non-database columns. TypeORM provides various methods for sorting, including based on fields that exist in the database. However, for custom fields (i.e., fields not directly present in the database table), we need to adopt specific strategies.

Example Explanation

Suppose we have an Employee entity containing firstName and lastName fields, and we need to sort employees based on the full name (fullName), but the database does not have a fullName field.

Solution 1: Creating Custom Fields Within the Query

In QueryBuilder, we can use the addSelect method to create a custom selection field and then sort based on it. For example:

typescript
import { getRepository } from "typeorm";

const employeeRepository = getRepository(Employee);

const employees = await employeeRepository
  .createQueryBuilder("employee")
  .select("employee.firstName")
  .addSelect("employee.lastName")
  .addSelect("CONCAT(employee.firstName, ' ', employee.lastName)", "fullName")
  .orderBy("fullName", "ASC")
  .getMany();

Here, we use the CONCAT function to create a new column fullName, and then sort using this newly generated column in orderBy.

Solution 2: Defining Virtual Fields in the Entity

If the sorting logic is complex or needs to be used in multiple places, define a virtual field in the entity class and use TypeORM's @AfterLoad() decorator to compute the value of this field. Then sort in the service layer as follows:

typescript
import { Entity, PrimaryGeneratedColumn, Column, AfterLoad } from "typeorm";

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

    @Column()
    firstName: string;

    @Column()
    lastName: string;

    fullName: string;  // virtual field, not corresponding to the database

    @AfterLoad()
    setComputed() {
        this.fullName = `${this.firstName} ${this.lastName}`;
    }
}

// In the service layer, use JavaScript's sort function for sorting
const employees = await employeeRepository.find();
employees.sort((a, b) => a.fullName.localeCompare(b.fullName));

In this example, the fullName field is computed after the entity is loaded, and then we perform sorting at the application level.

Conclusion

For sorting on custom fields, TypeORM provides flexible methods to handle these cases. You can choose to handle it at the database query level or at the application level, depending on your specific requirements and performance considerations. When dealing with large datasets or performance-critical applications, it is generally better to resolve sorting issues at the database level.