How to write where condition, for date less than or equal to some value, in nest.js TypeORM?

ONLINECAST

2 min read

·

06-10-2024

8

0

Share

Filtering Your Nest.js TypeORM Queries: Dates Less Than or Equal To

Need to fetch data from your database based on a date range? TypeORM in Nest.js offers powerful ways to filter your queries. This article guides you through the process of retrieving data where the date is less than or equal to a specific value.

Scenario: Finding All Records Before a Specific Date

Imagine you're working on a customer relationship management (CRM) system. You need to find all customers who registered before a certain date to send them a special offer.

Here's a typical scenario using a Customer entity:

import { Entity, Column, PrimaryGeneratedColumn, CreateDateColumn } from 'typeorm';

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

  @Column()
  name: string;

  @CreateDateColumn()
  registrationDate: Date;
}

You want to retrieve all Customer records where registrationDate is less than or equal to new Date('2023-12-31').

The Power of TypeORM's QueryBuilder

TypeORM's QueryBuilder is a versatile tool for constructing complex queries. Here's how to achieve our filtering requirement:

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Customer } from './customer.entity';

@Injectable()
export class CustomerService {
  constructor(
    @InjectRepository(Customer)
    private readonly customerRepository: Repository<Customer>,
  ) {}

  async getCustomersBeforeDate(date: Date): Promise<Customer[]> {
    const customers = await this.customerRepository
      .createQueryBuilder('customer')
      .where('customer.registrationDate <= :date', { date })
      .getMany();

    return customers;
  }
}

Explanation:

1. We use the createQueryBuilder method to initiate a query builder instance.
2. where clause defines the filtering condition using SQL syntax. Here, we're comparing the registrationDate column with the provided date parameter using the less than or equal to operator (<=).
3. The :date placeholder in the where clause is replaced with the actual date value passed to the getCustomersBeforeDate function.

Alternative Approach: Using the find Method

For simpler queries, you can directly use the find method with a where condition object:

async getCustomersBeforeDate(date: Date): Promise<Customer[]> {
  return this.customerRepository.find({
    where: { registrationDate: LessThanOrEqual(date) },
  });
}

Here, LessThanOrEqual is a helper function provided by TypeORM for date comparisons.

Key Considerations:

• Date Formatting: When working with dates in queries, ensure consistency in formatting. Use Date objects in your code and make sure your database stores dates in a compatible format.
• Performance Optimization: For large datasets, consider adding an index to the registrationDate column to enhance the speed of your queries.
• Error Handling: Include robust error handling mechanisms to gracefully manage potential issues during database interaction.

Conclusion

TypeORM provides powerful mechanisms for filtering your data based on dates. Utilizing QueryBuilder or the find method with appropriate conditions allows you to retrieve data efficiently and effectively. Remember to prioritize clean code, proper formatting, and error handling to ensure your queries are reliable and performant.