How to apply where-criteria to the same field more than once in TypeORM?

In TypeORM, you can apply multiple where conditions to the same field in various ways, depending on whether you construct the query using QueryBuilder or the Repository API. Here are two common approaches:

Using QueryBuilder

When using QueryBuilder, you can chain multiple where or andWhere statements to add multiple conditions to the same field. For example, if you want to query an entity named User with multiple different age ranges, you can do the following:

typescript
const users = await connection
  .getRepository(User)
  .createQueryBuilder("user")
  .where("user.age > :minAge", { minAge: 18 })
  .andWhere("user.age < :maxAge", { maxAge: 30 })
  .getMany();

In this example, we first define the condition that the age must exceed 18 using where, then add the condition that the age must be below 30 using andWhere.

Using Repository API

With the Repository API, you can construct an object containing multiple conditions. For instance, to find all users named 'John' with ages between 20 and 30, you can build the query as follows:

typescript
const users = await userRepository.find({
  where: [
    { name: 'John', age: MoreThan(20) },
    { name: 'John', age: LessThan(30) }
  ]
});

This query returns all users named 'John' whose ages are greater than 20 and less than 30.

Using Native SQL

For more complex queries or specific SQL statements, native SQL provides flexibility:

typescript
const users = await connection
  .getRepository(User)
  .createQueryBuilder("user")
  .where("user.name = :name AND user.age > :minAge AND user.age < :maxAge", {
    name: "John",
    minAge: 20,
    maxAge: 30
  })
  .getMany();

Here, all conditions are defined within a single where clause using named parameters to mitigate SQL injection risks.

Note that when constructing these queries, always consider SQL injection risks and use TypeORM's parameter replacement features to avoid embedding user input directly into SQL statements. Additionally, methods like MoreThan and LessThan are TypeORM-specific utilities that simplify building comparison queries.