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
Copy 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)
Copy 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".
Copy userRepository .find ({
join : {
alias : "user" ,
leftJoinAndSelect : {
profile : "user.profile" ,
photo : "user.photos" ,
video : "user.videos"
}
}
});
where
- simple conditions by which entity should be queried.
Copy 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:
Copy userRepository .find ({ where : { name : { first : "Timber" , last : "Saw" } } });
Querying with OR operator:
Copy userRepository .find ({
where : [
{ firstName : "Timber" , lastName : "Saw" } ,
{ firstName : "Stan" , lastName : "Lee" }
]
});
will execute following query:
Copy SELECT * FROM "user" WHERE ("firstName" = 'Timber' AND "lastName" = 'Saw') OR ("firstName" = 'Stan' AND "lastName" = 'Lee')
Copy userRepository .find ({
order : {
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.
Copy userRepository .find ({
withDeleted : true
});
find
methods which return multiple entities (find
, findAndCount
, findByIds
) also accept following options:
skip
- offset (paginated) from where entities should be taken.
Copy userRepository .find ({
skip : 5
});
take
- limit (paginated) - max number of entities that should be taken.
Copy 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.'
Copy userRepository .find ({
order : {
columnName : 'ASC'
} ,
skip : 0 ,
take : 10
})
cache
- Enables or disables query result caching. See caching for more information and options.
Copy 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:
Copy { mode : "optimistic" , version : number | Date }
or
Copy { mode : "pessimistic_read" | "pessimistic_write" | "dirty_read" | "pessimistic_partial_write" | "pessimistic_write_or_fail" }
for example:
Copy userRepository .findOne ( 1 , {
lock : { mode : "optimistic" , version : 1 }
})
pessimistic_partial_write
and pessimistic_write_or_fail
are supported only on Postgres and are equivalents of SELECT .. FOR UPDATE SKIP LOCKED
and SELECT .. FOR UPDATE NOWAIT
, accordingly.
Complete example of find options:
Copy 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:
Copy import {Not} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
title : Not ( "About #1" )
})
will execute following query:
Copy SELECT * FROM "post" WHERE "title" != 'About #1'
Copy import {LessThan} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
likes : LessThan ( 10 )
});
will execute following query:
Copy SELECT * FROM "post" WHERE "likes" < 10
Copy import {LessThanOrEqual} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
likes : LessThanOrEqual ( 10 )
});
will execute following query:
Copy SELECT * FROM "post" WHERE "likes" <= 10
Copy import {MoreThan} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
likes : MoreThan ( 10 )
});
will execute following query:
Copy SELECT * FROM "post" WHERE "likes" > 10
Copy import {MoreThanOrEqual} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
likes : MoreThanOrEqual ( 10 )
});
will execute following query:
Copy SELECT * FROM "post" WHERE "likes" >= 10
Copy import {Equal} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
title : Equal ( "About #2" )
});
will execute following query:
Copy SELECT * FROM "post" WHERE "title" = 'About #2'
Copy import {Like} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
title : Like ( "%out #%" )
});
will execute following query:
Copy SELECT * FROM "post" WHERE "title" LIKE '%out #%'
Copy import {Between} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
likes : Between ( 1 , 10 )
});
will execute following query:
Copy SELECT * FROM "post" WHERE "likes" BETWEEN 1 AND 10
Copy import {In} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
title : In ([ "About #2" , "About #3" ])
});
will execute following query:
Copy SELECT * FROM "post" WHERE "title" IN ( 'About #2' , 'About #3' )
Copy import {Any} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
title : Any ([ "About #2" , "About #3" ])
});
will execute following query (Postgres notation):
Copy SELECT * FROM "post" WHERE "title" = ANY(['About #2','About #3'])
Copy import {IsNull} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
title : IsNull ()
});
will execute following query:
Copy SELECT * FROM "post" WHERE "title" IS NULL
Copy import {Raw} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
likes : Raw ( "dislikes - 4" )
});
will execute following query:
Copy 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.
Copy import {Raw} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
currentDate : Raw (alias => ` ${ alias } > NOW()` )
});
will execute following query:
Copy SELECT * 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.
Copy import {Raw} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
currentDate : Raw (alias => ` ${ alias } > ':date'` , { date : "2020-10-06" })
});
will execute following query:
Copy SELECT * 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:
Copy import {Raw} from "typeorm" ;
const loadedPosts = await connection .getRepository (Post) .find ({
title: Raw(alias =>`${alias} IN (:...titles)`, { titles: ["Go To Statement Considered Harmful", "Structured Programming"] })
});
will execute following query:
Copy SELECT * FROM "post" WHERE "titles" IN ( 'Go To Statement Considered Harmful' , 'Structured Programming' )
Combining Advanced Options
Also you can combine these operators with Not
operator:
Copy 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:
Copy SELECT * FROM "post" WHERE NOT ( "likes" > 10 ) AND NOT ( "title" = 'About #2' )