Basic options

All repository and manager .find* methods accept special options you can use to query data you need without using QueryBuilder:

• select – indicates which properties of the main object must be selected

userRepository.find({
    select: {
        firstName: true,
        lastName: true,
    },
})

will execute following query:

SELECT "firstName", "lastName" FROM "user"

• relations – relations needs to be loaded with the main entity. Sub-relations can also be loaded (shorthand for join and leftJoinAndSelect)

userRepository.find({
    relations: {
        profile: true,
        photos: true,
        videos: true,
    },
})
userRepository.find({
    relations: {
        profile: true,
        photos: true,
        videos: {
            videoAttributes: true,
        },
    },
})

will execute following queries:

SELECT * FROM "user"
LEFT JOIN "profile" ON "profile"."id" = "user"."profileId"
LEFT JOIN "photos" ON "photos"."id" = "user"."photoId"
LEFT JOIN "videos" ON "videos"."id" = "user"."videoId"

SELECT * FROM "user"
LEFT JOIN "profile" ON "profile"."id" = "user"."profileId"
LEFT JOIN "photos" ON "photos"."id" = "user"."photoId"
LEFT JOIN "videos" ON "videos"."id" = "user"."videoId"
LEFT JOIN "video_attributes" ON "video_attributes"."id" = "videos"."video_attributesId"

• where – simple conditions by which entity should be queried.

userRepository.find({
    where: {
        firstName: "Timber",
        lastName: "Saw",
    },
})

will execute following query:

SELECT * FROM "user"
WHERE "firstName" = 'Timber' AND "lastName" = 'Saw'

Querying a column from an embedded entity should be done with respect to the hierarchy in which it was defined. Example:

userRepository.find({
    relations: {
        project: true,
    },
    where: {
        project: {
            name: "TypeORM",
            initials: "TORM",
        },
    },
})

will execute following query:

SELECT * FROM "user"
LEFT JOIN "project" ON "project"."id" = "user"."projectId"
WHERE "project"."name" = 'TypeORM' AND "project"."initials" = 'TORM'

Querying with OR operator:

userRepository.find({
    where: [
        { firstName: "Timber", lastName: "Saw" },
        { firstName: "Stan", lastName: "Lee" },
    ],
})

will execute following query:

SELECT * FROM "user" WHERE ("firstName" = 'Timber' AND "lastName" = 'Saw') OR ("firstName" = 'Stan' AND "lastName" = 'Lee')

• order – selection order.

userRepository.find({
    order: {
        name: "ASC",
        id: "DESC",
    },
})

will execute following query:

SELECT * FROM "user"
ORDER BY "name" ASC, "id" DESC

• withDeleted – include entities which have been soft deleted with softDelete or softRemove, e.g. have their @DeleteDateColumn column set. By default, soft deleted entities are not included.

userRepository.find({
    withDeleted: true,
})

find* methods which return multiple entities (find, findBy, findAndCount, findAndCountBy) also accept following options:

• skip – offset (paginated) from where entities should be taken.

userRepository.find({
    skip: 5,
})

SELECT * FROM "user"
OFFSET 5

• take – limit (paginated) – max number of entities that should be taken.

userRepository.find({
    take: 10,
})

will execute following query:

SELECT * FROM "user"
LIMIT 10

** skip and take should be used together

** If you are using typeorm with MSSQL, and want to use take or limit, you need to use order as well or you will receive the following error: 'Invalid usage of the option NEXT in the FETCH statement.'

userRepository.find({
    order: {
        columnName: "ASC",
    },
    skip: 0,
    take: 10,
})

will execute following query:

SELECT * FROM "user"
ORDER BY "columnName" ASC
LIMIT 10 OFFSET 0

 Advanced options

TypeORM provides a lot of built-in operators that can be used to create more complex comparisons:

• Not

import { Not } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    title: Not("About #1"),
})

will execute following query:

SELECT * FROM "post" WHERE "title" != 'About #1'

• LessThan

import { LessThan } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    likes: LessThan(10),
})

will execute following query:

SELECT * FROM "post" WHERE "likes" < 10

• LessThanOrEqual

import { LessThanOrEqual } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    likes: LessThanOrEqual(10),
})

will execute following query:

SELECT * FROM "post" WHERE "likes" <= 10

• MoreThan

import { MoreThan } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    likes: MoreThan(10),
})

will execute following query:

SELECT * FROM "post" WHERE "likes" > 10

• MoreThanOrEqual

import { MoreThanOrEqual } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    likes: MoreThanOrEqual(10),
})

will execute following query:

SELECT * FROM "post" WHERE "likes" >= 10

• Equal

import { Equal } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    title: Equal("About #2"),
})

will execute following query:

SELECT * FROM "post" WHERE "title" = 'About #2'

• Like

import { Like } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    title: Like("%out #%"),
})

will execute following query:

SELECT * FROM "post" WHERE "title" LIKE '%out #%'

• ILike

import { ILike } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    title: ILike("%out #%"),
})

will execute following query:

SELECT * FROM "post" WHERE "title" ILIKE '%out #%'

• Between

import { Between } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    likes: Between(1, 10),
})

will execute following query:

SELECT * FROM "post" WHERE "likes" BETWEEN 1 AND 10

• In

import { In } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    title: In(["About #2", "About #3"]),
})

will execute following query:

SELECT * FROM "post" WHERE "title" IN ('About #2','About #3')

• Any

import { Any } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    title: Any(["About #2", "About #3"]),
})

will execute following query (Postgres notation):

SELECT * FROM "post" WHERE "title" = ANY(['About #2','About #3'])

• IsNull

import { IsNull } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    title: IsNull(),
})

will execute following query:

SELECT * FROM "post" WHERE "title" IS NULL

Combining Advanced Options

Also you can combine these operators with Not operator:

import { Not, MoreThan, Equal } from "typeorm"

const loadedPosts = await dataSource.getRepository(Post).findBy({
    likes: Not(MoreThan(10)),
    title: Not(Equal("About #2")),
})

will execute following query:

SELECT * FROM "post" WHERE NOT("likes" > 10) AND NOT("title" = 'About #2')