Find 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
1
userRepository.find({ select: ["firstName", "lastName"] });
Copied!
will execute following query:
1
SELECT "firstName", "lastName" FROM "user"
Copied!
    relations - relations needs to be loaded with the main entity. Sub-relations can also be loaded (shorthand for join and leftJoinAndSelect)
1
userRepository.find({ relations: ["profile", "photos", "videos"] });
2
userRepository.find({
3
relations: ["profile", "photos", "videos", "videos.video_attributes"],
4
});
Copied!
will execute following queries:
1
SELECT * FROM "user"
2
LEFT JOIN "profile" ON "profile"."id" = "user"."profileId"
3
LEFT JOIN "photos" ON "photos"."id" = "user"."photoId"
4
LEFT JOIN "videos" ON "videos"."id" = "user"."videoId"
5
​
6
SELECT * FROM "user"
7
LEFT JOIN "profile" ON "profile"."id" = "user"."profileId"
8
LEFT JOIN "photos" ON "photos"."id" = "user"."photoId"
9
LEFT JOIN "videos" ON "videos"."id" = "user"."videoId"
10
LEFT JOIN "video_attributes" ON "video_attributes"."id" = "videos"."video_attributesId"
Copied!
    join - joins needs to be performed for the entity. Extended version of "relations".
1
userRepository.find({
2
join: {
3
alias: "user",
4
leftJoinAndSelect: {
5
profile: "user.profile",
6
photo: "user.photos",
7
video: "user.videos",
8
},
9
},
10
});
Copied!
will execute following query:
1
SELECT * FROM "user" "user"
2
LEFT JOIN "profile" ON "profile"."id" = "user"."profile"
3
LEFT JOIN "photo" ON "photo"."id" = "user"."photos"
4
LEFT JOIN "video" ON "video"."id" = "user"."videos"
Copied!
    where - simple conditions by which entity should be queried.
1
userRepository.find({ where: { firstName: "Timber", lastName: "Saw" } });
Copied!
will execute following query:
1
SELECT * FROM "user"
2
WHERE "firstName" = 'Timber' AND "lastName" = 'Saw'
Copied!
Querying a column from an embedded entity should be done with respect to the hierarchy in which it was defined. Example:
1
userRepository.find({
2
where: {
3
project: { name: "TypeORM", initials: "TORM" },
4
},
5
relations: ["project"],
6
});
Copied!
will execute following query:
1
SELECT * FROM "user"
2
WHERE "project"."name" = 'TypeORM' AND "project"."initials" = 'TORM'
3
LEFT JOIN "project" ON "project"."id" = "user"."projectId"
Copied!
Querying with OR operator:
1
userRepository.find({
2
where: [
3
{ firstName: "Timber", lastName: "Saw" },
4
{ firstName: "Stan", lastName: "Lee" },
5
],
6
});
Copied!
will execute following query:
1
SELECT * FROM "user" WHERE ("firstName" = 'Timber' AND "lastName" = 'Saw') OR ("firstName" = 'Stan' AND "lastName" = 'Lee')
Copied!
    order - selection order.
1
userRepository.find({
2
order: {
3
name: "ASC",
4
id: "DESC",
5
},
6
});
Copied!
will execute following query:
1
SELECT * FROM "user"
2
ORDER BY "name" ASC, "id" DESC
Copied!
    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.
1
userRepository.find({
2
withDeleted: true,
3
});
Copied!
find methods which return multiple entities (find, findAndCount, findByIds) also accept following options:
    skip - offset (paginated) from where entities should be taken.
1
userRepository.find({
2
skip: 5,
3
});
Copied!
1
SELECT * FROM "user"
2
OFFSET 5
Copied!
    take - limit (paginated) - max number of entities that should be taken.
1
userRepository.find({
2
take: 10,
3
});
Copied!
will execute following query:
1
SELECT * FROM "user"
2
LIMIT 10
Copied!
** 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.'
1
userRepository.find({
2
order: {
3
columnName: "ASC",
4
},
5
skip: 0,
6
take: 10,
7
});
Copied!
will execute following query:
1
SELECT * FROM "user"
2
ORDER BY "columnName" ASC
3
LIMIT 10 OFFSET 0
Copied!
    cache - Enables or disables query result caching. See caching for more information and options.
1
userRepository.find({
2
cache: true,
3
});
Copied!
    lock - Enables locking mechanism for query. Can be used only in findOne method. lock is an object which can be defined as:
1
{ mode: "optimistic", version: number|Date }
Copied!
or
1
{
2
mode: "pessimistic_read" |
3
"pessimistic_write" |
4
"dirty_read" |
5
"pessimistic_partial_write" |
6
"pessimistic_write_or_fail" |
7
"for_no_key_update";
8
}
Copied!
for example:
1
userRepository.findOne(1, {
2
lock: { mode: "optimistic", version: 1 },
3
});
Copied!
Support of lock modes, and SQL statements they translate to, are listed in the table below (blank cell denotes unsupported). When specified lock mode is not supported, a LockNotSupportedOnGivenDriverError error will be thrown.
1
| | pessimistic_read | pessimistic_write | dirty_read | pessimistic_partial_write | pessimistic_write_or_fail | for_no_key_update |
2
| --------------- | -------------------- | ----------------------- | ------------- | --------------------------- | --------------------------- | ------------------- |
3
| MySQL | LOCK IN SHARE MODE | FOR UPDATE | (nothing) | FOR UPDATE SKIP LOCKED | FOR UPDATE NOWAIT | |
4
| Postgres | FOR SHARE | FOR UPDATE | (nothing) | FOR UPDATE SKIP LOCKED | FOR UPDATE NOWAIT | FOR NO KEY UPDATE |
5
| Oracle | FOR UPDATE | FOR UPDATE | (nothing) | | | |
6
| SQL Server | WITH (HOLDLOCK, ROWLOCK) | WITH (UPDLOCK, ROWLOCK) | WITH (NOLOCK) | | | |
7
| AuroraDataApi | LOCK IN SHARE MODE | FOR UPDATE | (nothing) | | | |
Copied!
Complete example of find options:
1
userRepository.find({
2
select: ["firstName", "lastName"],
3
relations: ["profile", "photos", "videos"],
4
where: {
5
firstName: "Timber",
6
lastName: "Saw",
7
profile: {
8
userName: "tshaw",
9
},
10
},
11
order: {
12
name: "ASC",
13
id: "DESC",
14
},
15
skip: 5,
16
take: 10,
17
cache: true,
18
});
Copied!

Advanced options

TypeORM provides a lot of built-in operators that can be used to create more complex comparisons:
    Not
1
import { Not } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
title: Not("About #1"),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "title" != 'About #1'
Copied!
    LessThan
1
import { LessThan } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
likes: LessThan(10),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "likes" < 10
Copied!
    LessThanOrEqual
1
import { LessThanOrEqual } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
likes: LessThanOrEqual(10),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "likes" <= 10
Copied!
    MoreThan
1
import { MoreThan } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
likes: MoreThan(10),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "likes" > 10
Copied!
    MoreThanOrEqual
1
import { MoreThanOrEqual } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
likes: MoreThanOrEqual(10),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "likes" >= 10
Copied!
    Equal
1
import { Equal } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
title: Equal("About #2"),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "title" = 'About #2'
Copied!
    Like
1
import { Like } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
title: Like("%out #%"),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "title" LIKE '%out #%'
Copied!
    ILike
1
import { ILike } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
title: ILike("%out #%"),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "title" ILIKE '%out #%'
Copied!
    Between
1
import { Between } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
likes: Between(1, 10),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "likes" BETWEEN 1 AND 10
Copied!
    In
1
import { In } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
title: In(["About #2", "About #3"]),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "title" IN ('About #2','About #3')
Copied!
    Any
1
import { Any } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
title: Any(["About #2", "About #3"]),
5
});
Copied!
will execute following query (Postgres notation):
1
SELECT * FROM "post" WHERE "title" = ANY(['About #2','About #3'])
Copied!
    IsNull
1
import { IsNull } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
title: IsNull(),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "title" IS NULL
Copied!
    Raw
1
import { Raw } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
likes: Raw("dislikes - 4"),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "likes" = "dislikes" - 4
Copied!
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.
1
import { Raw } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
currentDate: Raw((alias) => `${alias} > NOW()`),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "currentDate" > NOW()
Copied!
If you need to provide user input, you should not include the user input directly in your query as this may create a SQL injection vulnerability. Instead, you can use the second argument of the Raw function to provide a list of parameters to bind to the query.
1
import { Raw } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
currentDate: Raw((alias) => `${alias} > :date`, { date: "2020-10-06" }),
5
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "currentDate" > '2020-10-06'
Copied!
If you need to provide user input that is an array, you can bind them as a list of values in the SQL statement by using the special expression syntax:
1
import { Raw } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
title: Raw((alias) => `${alias} IN (:...titles)`, {
5
titles: [
6
"Go To Statement Considered Harmful",
7
"Structured Programming",
8
],
9
}),
10
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE "titles" IN ('Go To Statement Considered Harmful', 'Structured Programming')
Copied!

Combining Advanced Options

Also you can combine these operators with Not operator:
1
import { Not, MoreThan, Equal } from "typeorm";
2
​
3
const loadedPosts = await connection.getRepository(Post).find({
4
likes: Not(MoreThan(10)),
5
title: Not(Equal("About #2")),
6
});
Copied!
will execute following query:
1
SELECT * FROM "post" WHERE NOT("likes" > 10) AND NOT("title" = 'About #2')
Copied!
Last modified 1mo ago