TypeORM: AND & OR operators

Updated: January 30, 2024 By: A Goodman 4 comments

This concise, straight-to-the-point article gives a few quick examples of using AND and OR operators in TypeORM. No more rambling; let’s get our hands dirty by writing some code.

Creating a sample entity

In the coming examples, we’ll use an entity named Product (with four columns: id, name, price, and color):

// Product entity
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm'

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

    @Column()
    name: string;

    @Column()
    price: number;

    @Column()
    color: string;
}

Using Find Options

AND operator

This code will find all products that satisfy both conditions: the price is less than 100 and the color is blue:

const productRepository = dataSource.getRepository(Product);
const result = await productRepository.find({
        where: {
            price: LessThan(100),
            color: 'blue'
        }
})

OR operator

You can query with the OR operator using square brackets.

This code will find products with prices less than 100 OR whose color is red:

const productRepository = dataSource.getRepository(Product);
const result = await productRepository.find({
        where: [
            {price: LessThan(100)},
            {color: 'red'}
        ]
})

Using Query Builder

AND operator

This code will find all products that satisfy both conditions: the price is less than 200 AND the color is yellow:

const productRepository = dataSource.getRepository(Product);
const result = await productRepository
        .createQueryBuilder('product')
        .where('product.price < :price', { price: 200 })
        .andWhere('product.color = :color', { color: 'yellow' })
        .getMany();

OR operator

This code will find all products that satisfy one of these conditions: the price is less than 200 OR the color is yellow:

const productRepository = dataSource.getRepository(Product);
const result = await productRepository
        .createQueryBuilder('product')
        .where('product.price < :price', { price: 100 })
        .orWhere('product.color = :color', { color: 'yellow' })
        .getMany();

Conclusion

We’ve walked through some examples about querying data with AND and OR operators. This knowledge is very important for backend and mobile developers who want to manipulate databases with TypeORM. If you’d like to explore more new and interesting things about TypeORM and other databases’ relevant stuff, 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
4 Comments
Inline Feedbacks
View all comments
Patricio
Patricio
1 year ago

How can you nest and OR condition inside and AND condition using the find approach?

Patricio
Patricio
1 year ago
Reply to  Patricio

Found my own answer. You may have to be repetitive but it’s the only way. For A or B and C, would be where: [{A,C},{B,C}].

Abel Matos
Abel Matos
9 months ago
Reply to  Patricio

In this case, I assigned colC, but can be whatever column inside the query (A, B or C).
If you will assign C in a not new where object, you must remove others inside the query and then do it

delete where.colA;
delete where.colB;
where.colC = Raw(....);

Abel Matos
Abel Matos
9 months ago
Reply to  Patricio

For query “where (colA=valueA or colB=valueB) and colC=valueC” we can use Raw operator: import { Raw } from 'typeorm'; ... where = { colC: Raw((columnAlias) => {   const aliasParts = columnAlias.split(".");   const tableAlias = aliasParts.length > 1 ? aliasParts[0] + "." : "";   return((${tableAlias}colA = :valueA or ${tableAlias}colB = :valueB)… Read more »

Related Articles