Find Options
- Find Options
- Basic options

Find Options

Basic options
Advanced options

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", "lastName"] });

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", "photos", "videos"] });
userRepository.find({ relations: ["profile", "photos", "videos", "videos.video_attributes"] });

join - joins needs to be performed for the entity. Extended version of “relations”.

userRepository.find({
    join: {
        alias: "user",
        leftJoinAndSelect: {
            profile: "user.profile",
            photo: "user.photos",
            video: "user.videos"
        }
    }
});

where - simple conditions by which entity should be queried.

userRepository.find({ where: { firstName: "Timber", 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({ where: { name: { first: "Timber", last: "Saw" } } });

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"
    }
});

find methods which return multiple entities (find, findAndCount, findByIds) also accept following options:

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

userRepository.find({
    skip: 5
});

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

userRepository.find({
    take: 10
});

** 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
})

cache - Enables or disables query result caching. See caching for more information and options.

userRepository.find({
    cache: true
})

lock - Enables locking mechanism for query. Can be used only in findOne method. lock is an object which can be defined as: { mode: "optimistic", version: number|Date } or ``` { mode: "pessimistic_read"|"pessimistic_write"|"dirty_read" }


for example:

userRepository.findOne(1, { lock: { mode: "optimistic", version: 1 } })


Complete example of find options:

userRepository.find({ select: ["firstName", "lastName"], relations: ["profile", "photos", "videos"], where: { firstName: "Timber", lastName: "Saw" }, order: { name: "ASC", id: "DESC" }, skip: 5, take: 10, cache: true });


## 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 connection.getRepository(Post).find({ title: Not("About #1") })


will execute following query:

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


  * `LessThan`

import {LessThan} from "typeorm";

const loadedPosts = await connection.getRepository(Post).find({ likes: LessThan(10) });


will execute following query:

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


  * `LessThanOrEqual`

import {LessThanOrEqual} from "typeorm";

const loadedPosts = await connection.getRepository(Post).find({ likes: LessThanOrEqual(10) });


will execute following query:

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


  * `MoreThan`

import {MoreThan} from "typeorm";

const loadedPosts = await connection.getRepository(Post).find({ likes: MoreThan(10) });


will execute following query:

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


  * `MoreThanOrEqual`

import {MoreThanOrEqual} from "typeorm";

const loadedPosts = await connection.getRepository(Post).find({ likes: MoreThanOrEqual(10) });


will execute following query:

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


  * `Equal`

import {Equal} from "typeorm";

const loadedPosts = await connection.getRepository(Post).find({ title: Equal("About #2") });


will execute following query:

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


  * `Like`

import {Like} from "typeorm";

const loadedPosts = await connection.getRepository(Post).find({ title: Like("%out #%") });


will execute following query:

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


  * `Between`

import {Between} from "typeorm";

const loadedPosts = await connection.getRepository(Post).find({ 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 connection.getRepository(Post).find({ 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 connection.getRepository(Post).find({ 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 connection.getRepository(Post).find({ title: IsNull() });


will execute following query:

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


  * `Raw`

import {Raw} from "typeorm";

const loadedPosts = await connection.getRepository(Post).find({ likes: Raw("dislikes - 4") });


will execute following query:

SELECT * FROM "post" WHERE "likes" = "dislikes" - 4


In the simplest case, a raw query is inserted immediately after the equal symbol. But you can also completely rewrite the comparison logic using the function.

import {Raw} from "typeorm";

const loadedPosts = await connection.getRepository(Post).find({ currentDate: Raw(alias =>${alias} > NOW()) });


will execute following query:

SELECT * FROM "post" WHERE "currentDate" > NOW()


> Note: beware with `Raw` operator. It executes pure SQL from supplied expression and should not contain a user input, otherwise it will lead to SQL-injection.

Also you can combine these operators with `Not` operator:

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

const loadedPosts = await connection.getRepository(Post).find({ 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') ```