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 selected1userRepository.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)1userRepository.find({ relations: ["profile", "photos", "videos"] }); 2userRepository.find({ relations: ["profile", "photos", "videos", "videos.video_attributes"] });
join - join needs to be performed for the entity. Extended version of "relations".1userRepository.find({ 2 join: { 3 alias: "user", 4 leftJoinAndSelect: { 5 profile: "user.profile", 6 photo: "user.photos", 7 video: "user.videos" 8 } 9 } 10});
where - simple conditions by which entity should be queried.1userRepository.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:
1userRepository.find({ where: { name: { first: "Timber", last: "Saw" } } });
Querying with OR operator:
1userRepository.find({ 2 where: [ 3 { firstName: "Timber", lastName: "Saw" }, 4 { firstName: "Stan", lastName: "Lee" } 5 ] 6});
will execute the following query:
1SELECT * FROM "user" WHERE ("firstName" = 'Timber' AND "lastName" = 'Saw') OR ("firstName" = 'Stan' AND "lastName" = 'Lee')
order - selection order.1userRepository.find({ 2 order: { 3 name: "ASC", 4 id: "DESC" 5 } 6});
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.1userRepository.find({ 2 withDeleted: true 3});
find methods which return multiple entities (find, findAndCount, findByIds) also accept the following options:
skip - offset (paginated) from where entities should be taken.1userRepository.find({ 2 skip: 5 3});
take - limit (paginated) - max number of entities that should be taken.1userRepository.find({ 2 take: 10 3});
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.'1userRepository.find({ 2 order: { 3 columnName: 'ASC' 4 }, 5 skip: 0, 6 take: 10 7})
cache - Enables or disables query result caching. 1userRepository.find({ 2 cache: true 3})
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 }
or
1{ mode: "pessimistic_read"|"pessimistic_write"|"dirty_read"|"pessimistic_partial_write"|"pessimistic_write_or_fail"|"for_no_key_update" }
for example:
1userRepository.findOne(1, { 2 lock: { mode: "optimistic", version: 1 } 3})
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) | | | | 8
Complete example of find options:
1userRepository.find({ 2 select: ["firstName", "lastName"], 3 relations: ["profile", "photos", "videos"], 4 where: { 5 firstName: "Timber", 6 lastName: "Saw" 7 }, 8 order: { 9 name: "ASC", 10 id: "DESC" 11 }, 12 skip: 5, 13 take: 10, 14 cache: true 15});
TypeORM provides a lot of built-in operators that can be used to create more complex comparisons:
Not1import {Not} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: Not("About #1") 5})
will execute the following query:
1SELECT * FROM "post" WHERE "title" != 'About #1'
LessThan1import {LessThan} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: LessThan(10) 5});
will execute the following query:
1SELECT * FROM "post" WHERE "likes" < 10
LessThanOrEqual1import {LessThanOrEqual} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: LessThanOrEqual(10) 5});
will execute the following query:
1SELECT * FROM "post" WHERE "likes" <= 10
MoreThan1import {MoreThan} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: MoreThan(10) 5});
will execute following query:
1SELECT * FROM "post" WHERE "likes" > 10
MoreThanOrEqual1import {MoreThanOrEqual} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: MoreThanOrEqual(10) 5});
will execute the following query:
1SELECT * FROM "post" WHERE "likes" >= 10
Equal1import {Equal} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: Equal("About #2") 5});
will execute the following query:
1SELECT * FROM "post" WHERE "title" = 'About #2'
Like1import {Like} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: Like("%out #%") 5});
will execute the following query:
1SELECT * FROM "post" WHERE "title" LIKE '%out #%'
ILike1import {ILike} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: ILike("%out #%") 5});
will execute the following query:
1SELECT * FROM "post" WHERE "title" ILIKE '%out #%'
Between1import {Between} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: Between(1, 10) 5});
will execute the following query:
1SELECT * FROM "post" WHERE "likes" BETWEEN 1 AND 10
In1import {In} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: In(["About #2", "About #3"]) 5});
will execute the following query:
1SELECT * FROM "post" WHERE "title" IN ('About #2','About #3')
Any1import {Any} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: Any(["About #2", "About #3"]) 5});
will execute the following query (Postgres notation):
1SELECT * FROM "post" WHERE "title" = ANY(['About #2','About #3'])
IsNull1import {IsNull} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: IsNull() 5});
will execute the following query:
1SELECT * FROM "post" WHERE "title" IS NULL
Raw1import {Raw} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: Raw("dislikes - 4") 5});
will execute the following query:
1SELECT * 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.
1import {Raw} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 currentDate: Raw(alias =>`${alias} > NOW()`) 5});
will execute the following query:
1SELECT * FROM "post" WHERE "currentDate" > NOW()
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.
1import {Raw} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 currentDate: Raw(alias =>`${alias} > ':date'`, { date: "2020-10-06" }) 5});
will execute the following query:
1SELECT * FROM "post" WHERE "currentDate" > '2020-10-06'
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:
1import {Raw} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 title: Raw(alias =>`${alias} IN (:...titles)`, { titles: ["Go To Statement Considered Harmful", "Structured Programming"] }) 5});
will execute the following query:
1SELECT * FROM "post" WHERE "titles" IN ('Go To Statement Considered Harmful', 'Structured Programming')
Also you can combine these operators with Not operator:
1import {Not, MoreThan, Equal} from "typeorm"; 2 3const loadedPosts = await connection.getRepository(Post).find({ 4 likes: Not(MoreThan(10)), 5 title: Not(Equal("About #2")) 6});
will execute the following query:
1SELECT * FROM "post" WHERE NOT("likes" > 10) AND NOT("title" = 'About #2')