QueryBuilder <Entity, RootAlias, Hint, Context>

SQL query builder with fluent interface.

const qb = orm.em.createQueryBuilder(Publisher);
qb.select('*')
  .where({
    name: 'test 123',
    type: PublisherType.GLOBAL,
  })
  .orderBy({
    name: QueryOrder.DESC,
    type: QueryOrder.ASC,
  })
  .limit(2, 1);

const publisher = await qb.getSingleResult();

Hierarchy

• QueryBuilder
  • SelectQueryBuilder
  • CountQueryBuilder

Index

Accessors

• alias
• helper
• mainAlias

Methods

• addSelect
• andHaving
• andWhere
• applyFilters
• as
• cache
• clone
• comment
• count
• delete
• distinct
• distinctOn
• execute
• from
• getCount
• getFormattedQuery
• getKnex
• getKnexQuery
• getLoggerContext
• getParams
• getQuery
• getResult
• getResultAndCount
• getResultList
• getSingleResult
• groupBy
• hasFlag
• having
• hintComment
• ignore
• indexHint
• innerJoin
• innerJoinAndSelect
• innerJoinLateral
• innerJoinLateralAndSelect
• insert
• join
• joinAndSelect
• leftJoin
• leftJoinAndSelect
• leftJoinLateral
• leftJoinLateralAndSelect
• limit
• merge
• offset
• onConflict
• orderBy
• orHaving
• orWhere
• returning
• select
• setFlag
• setFlushMode
• setLockMode
• setLoggerContext
• then
• toQuery
• truncate
• unsetFlag
• update
• where
• withSchema
• withSubQuery

Accessors

alias

• get alias(): string

• Returns string

helper

• get helper(): QueryBuilderHelper

• Returns QueryBuilderHelper

mainAlias

• get mainAlias(): Alias<Entity>

• Returns Alias<Entity>

Methods

addSelect

• addSelect(fields: Field<Entity> | Field<Entity>[]): SelectQueryBuilder<Entity, RootAlias, Hint, Context>

• Parameters

  • fields: Field<Entity> | Field<Entity>[]

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, Context>

andHaving

• andHaving(cond?: string | QBFilterQuery, params?: any[]): SelectQueryBuilder<Entity, RootAlias, Hint, Context>

• Parameters

  • optionalcond: string | QBFilterQuery

  • optionalparams: any[]

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, Context>

andWhere

• andWhere(cond: QBFilterQuery<Entity>): this
• andWhere(cond: string, params?: any[]): this

• Parameters

  • cond: QBFilterQuery<Entity>

  Returns this

applyFilters

• applyFilters(filterOptions?: boolean | string[] | Dictionary<boolean | Dictionary>): Promise<void>

• Apply filters to the QB where condition.

  ---

  Parameters

  • filterOptions: boolean | string[] | Dictionary<boolean | Dictionary> = {}

  Returns Promise<void>

as

• as(alias: string): QueryBuilder<any, any>

• Returns knex instance with sub-query aliased with given alias. You can provide EntityName.propName as alias, then the field name will be used based on the metadata

  ---

  Parameters

  • alias: string

  Returns QueryBuilder<any, any>

cache

• cache(config?: number | boolean | [string, number]): this

• Parameters

  • config: number | boolean | [string, number] = true

  Returns this

clone

• clone(reset?: boolean | string[]): QueryBuilder<Entity, never, never, never>

• Parameters

  • optionalreset: boolean | string[]

  Returns QueryBuilder<Entity, never, never, never>

comment

• comment(comment: string | string[]): this

• Prepend comment to the sql query using the syntax /* ... *‍/. Some characters are forbidden such as /*, *‍/ and ?.

  ---

  Parameters

  • comment: string | string[]

  Returns this

count

• count(field?: EntityKeyOrString<Entity> | EntityKeyOrString<Entity>[], distinct?: boolean): CountQueryBuilder<Entity>

• Parameters

  • optionalfield: EntityKeyOrString<Entity> | EntityKeyOrString<Entity>[]

  • distinct: boolean = false

  Returns CountQueryBuilder<Entity>

delete

• delete(cond?: QBFilterQuery): DeleteQueryBuilder<Entity>

• Parameters

  • optionalcond: QBFilterQuery

  Returns DeleteQueryBuilder<Entity>

distinct

• distinct(): SelectQueryBuilder<Entity, RootAlias, Hint, Context>

• Returns SelectQueryBuilder<Entity, RootAlias, Hint, Context>

distinctOn

• distinctOn(fields: EntityKeyOrString<Entity> | EntityKeyOrString<Entity>[]): SelectQueryBuilder<Entity, RootAlias, Hint, Context>

• postgres only

  ---

  Parameters

  • fields: EntityKeyOrString<Entity> | EntityKeyOrString<Entity>[]

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, Context>

execute

• execute(method?: get | all | run, options?: boolean | ExecuteOptions): Promise<U>

• Executes this QB and returns the raw results, mapped to the property names (unless disabled via last parameter). Use method to specify what kind of result you want to get (array/single/meta).

  ---

  Parameters

  • optionalmethod: get | all | run

  • optionaloptions: boolean | ExecuteOptions

  Returns Promise<U>

from

• from(target: QueryBuilder<Entity, never, never, never>, aliasName?: string): SelectQueryBuilder<Entity, RootAlias, Hint, Context>
• from(target: EntityName<Entity>): SelectQueryBuilder<Entity, RootAlias, Hint, Context>

• Specifies FROM which entity's table select/update/delete will be executed, removing all previously set FROM-s. Allows setting a main string alias of the selection data.

  ---

  Parameters

  • target: QueryBuilder<Entity, never, never, never>

  • optionalaliasName: string

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, Context>

getCount

• getCount(field?: EntityKeyOrString<Entity> | EntityKeyOrString<Entity>[], distinct?: boolean): Promise<number>

• Executes count query (without offset and limit), returning total count of results

  ---

  Parameters

  • optionalfield: EntityKeyOrString<Entity> | EntityKeyOrString<Entity>[]

  • optionaldistinct: boolean

  Returns Promise<number>

getFormattedQuery

• getFormattedQuery(): string

• Returns raw interpolated query string with all the parameters inlined.

  ---

  Returns string

getKnex

• getKnex(processVirtualEntity?: boolean): QueryBuilder<any, any>

• Parameters

  • processVirtualEntity: boolean = true

  Returns QueryBuilder<any, any>

getKnexQuery

• getKnexQuery(processVirtualEntity?: boolean): QueryBuilder<any, any>

• Parameters

  • processVirtualEntity: boolean = true

  Returns QueryBuilder<any, any>

getLoggerContext

• getLoggerContext(): T

• Gets logger context for this query builder.

  ---

  Returns T

getParams

• getParams(): readonly Value[]

• Returns the list of all parameters for this query.

  ---

  Returns readonly Value[]

getQuery

• getQuery(): string

• Returns the query with parameters as wildcards.

  ---

  Returns string

getResult

• getResult(): Promise<Loaded<Entity, Hint>[]>

• Alias for qb.getResultList()

  ---

  Returns Promise<Loaded<Entity, Hint>[]>

getResultAndCount

• getResultAndCount(): Promise<[Entity[], number]>

• Executes the query, returning both array of results and total count query (without offset and limit).

  ---

  Returns Promise<[Entity[], number]>

getResultList

• getResultList(limit?: number): Promise<Loaded<Entity, Hint>[]>

• Executes the query, returning array of results

  ---

  Parameters

  • optionallimit: number

  Returns Promise<Loaded<Entity, Hint>[]>

getSingleResult

• getSingleResult(): Promise<null | Entity>

• Executes the query, returning the first result or null

  ---

  Returns Promise<null | Entity>

groupBy

• groupBy(fields: EntityKeyOrString<Entity> | readonly EntityKeyOrString<Entity>[]): SelectQueryBuilder<Entity, RootAlias, Hint, Context>

• Parameters

  • fields: EntityKeyOrString<Entity> | readonly EntityKeyOrString<Entity>[]

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, Context>

hasFlag

• hasFlag(flag: QueryFlag): boolean

• Parameters

  • flag: QueryFlag

  Returns boolean

having

• having(cond?: string | QBFilterQuery, params?: any[], operator?: $and | $or): SelectQueryBuilder<Entity, RootAlias, Hint, Context>

• Parameters

  • cond: string | QBFilterQuery = {}

  • optionalparams: any[]

  • optionaloperator: $and | $or

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, Context>

hintComment

• hintComment(comment: string | string[]): this

• Add hints to the query using comment-like syntax /*+ ... *‍/. MySQL and Oracle use this syntax for optimizer hints. Also various DB proxies and routers use this syntax to pass hints to alter their behavior. In other dialects the hints are ignored as simple comments.

  ---

  Parameters

  • comment: string | string[]

  Returns this

ignore

• ignore(): this

• Returns this

indexHint

• indexHint(sql: string): this

• Adds index hint to the FROM clause.

  ---

  Parameters

  • sql: string

  Returns this

innerJoin

• innerJoin(field: QueryBuilder<any, any> | QueryBuilder<any, never, never, never> | Field, alias: Alias, cond?: QBFilterQuery, schema?: string): SelectQueryBuilder<Entity, RootAlias, Hint, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, false> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, false>[K] }>

• Parameters

  • field: QueryBuilder<any, any> | QueryBuilder<any, never, never, never> | Field

  • alias: Alias

  • cond: QBFilterQuery = {}

  • optionalschema: string

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, false> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, false>[K] }>

innerJoinAndSelect

• innerJoinAndSelect(field: Field | [field: Field, qb: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>], alias: Alias, cond?: QBFilterQuery, fields?: string[], schema?: string): SelectQueryBuilder<Entity, RootAlias, ModifyHint<RootAlias, Context, Hint, Field, true> & {}, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, true> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, true>[K] }>

• Parameters

  • field: Field | [field: Field, qb: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>]

  • alias: Alias

  • cond: QBFilterQuery = {}

  • optionalfields: string[]

  • optionalschema: string

  Returns SelectQueryBuilder<Entity, RootAlias, ModifyHint<RootAlias, Context, Hint, Field, true> & {}, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, true> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, true>[K] }>

innerJoinLateral

• innerJoinLateral(field: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>, alias: string, cond?: QBFilterQuery, schema?: string): this

• Parameters

  • field: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>

  • alias: string

  • cond: QBFilterQuery = {}

  • optionalschema: string

  Returns this

innerJoinLateralAndSelect

• innerJoinLateralAndSelect(field: [field: Field, qb: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>], alias: Alias, cond?: QBFilterQuery, fields?: string[], schema?: string): SelectQueryBuilder<Entity, RootAlias, ModifyHint<RootAlias, Context, Hint, Field, true> & {}, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, true> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, true>[K] }>

• Parameters

  • field: [field: Field, qb: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>]

  • alias: Alias

  • cond: QBFilterQuery = {}

  • optionalfields: string[]

  • optionalschema: string

  Returns SelectQueryBuilder<Entity, RootAlias, ModifyHint<RootAlias, Context, Hint, Field, true> & {}, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, true> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, true>[K] }>

insert

• insert(data: RequiredEntityData<Entity> | RequiredEntityData<Entity>[]): InsertQueryBuilder<Entity>

• Parameters

  • data: RequiredEntityData<Entity> | RequiredEntityData<Entity>[]

  Returns InsertQueryBuilder<Entity>

join

• join(field: QueryBuilder<any, any> | Field | QueryBuilder<any, never, never, never>, alias: Alias, cond?: QBFilterQuery, type?: JoinType, path?: string, schema?: string): SelectQueryBuilder<Entity, RootAlias, Hint, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, false> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, false>[K] }>

• Parameters

  • field: QueryBuilder<any, any> | Field | QueryBuilder<any, never, never, never>

  • alias: Alias

  • cond: QBFilterQuery = {}

  • type: JoinType = JoinType.innerJoin

  • optionalpath: string

  • optionalschema: string

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, false> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, false>[K] }>

joinAndSelect

• joinAndSelect(field: Field | [field: Field, qb: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>], alias: Alias, cond?: QBFilterQuery, type?: JoinType, path?: string, fields?: string[], schema?: string): SelectQueryBuilder<Entity, RootAlias, ModifyHint<RootAlias, Context, Hint, Field, true> & {}, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, true> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, true>[K] }>

• Parameters

  • field: Field | [field: Field, qb: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>]

  • alias: Alias

  • cond: QBFilterQuery = {}

  • type: JoinType = JoinType.innerJoin

  • optionalpath: string

  • optionalfields: string[]

  • optionalschema: string

  Returns SelectQueryBuilder<Entity, RootAlias, ModifyHint<RootAlias, Context, Hint, Field, true> & {}, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, true> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, true>[K] }>

leftJoin

• leftJoin(field: QueryBuilder<any, any> | QueryBuilder<any, never, never, never> | Field, alias: Alias, cond?: QBFilterQuery, schema?: string): SelectQueryBuilder<Entity, RootAlias, Hint, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, false> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, false>[K] }>

• Parameters

  • field: QueryBuilder<any, any> | QueryBuilder<any, never, never, never> | Field

  • alias: Alias

  • cond: QBFilterQuery = {}

  • optionalschema: string

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, false> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, false>[K] }>

leftJoinAndSelect

• leftJoinAndSelect(field: Field | [field: Field, qb: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>], alias: Alias, cond?: QBFilterQuery, fields?: string[], schema?: string): SelectQueryBuilder<Entity, RootAlias, ModifyHint<RootAlias, Context, Hint, Field, true> & {}, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, true> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, true>[K] }>

• Parameters

  • field: Field | [field: Field, qb: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>]

  • alias: Alias

  • cond: QBFilterQuery = {}

  • optionalfields: string[]

  • optionalschema: string

  Returns SelectQueryBuilder<Entity, RootAlias, ModifyHint<RootAlias, Context, Hint, Field, true> & {}, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, true> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, true>[K] }>

leftJoinLateral

• leftJoinLateral(field: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>, alias: string, cond?: QBFilterQuery, schema?: string): this

• Parameters

  • field: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>

  • alias: string

  • cond: QBFilterQuery = {}

  • optionalschema: string

  Returns this

leftJoinLateralAndSelect

• leftJoinLateralAndSelect(field: [field: Field, qb: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>], alias: Alias, cond?: QBFilterQuery, fields?: string[], schema?: string): SelectQueryBuilder<Entity, RootAlias, ModifyHint<RootAlias, Context, Hint, Field, true> & {}, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, true> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, true>[K] }>

• Parameters

  • field: [field: Field, qb: QueryBuilder<any, any> | QueryBuilder<any, never, never, never>]

  • alias: Alias

  • cond: QBFilterQuery = {}

  • optionalfields: string[]

  • optionalschema: string

  Returns SelectQueryBuilder<Entity, RootAlias, ModifyHint<RootAlias, Context, Hint, Field, true> & {}, { [ K in string | number | symbol ]: IsNever<Context, true, false> extends true ? AddToContext<GetType<Entity, object, Field>, object, Field, Alias, true> : Context & AddToContext<GetType<Entity, Context, Field>, Context, Field, Alias, true>[K] }>

limit

• limit(limit?: number, offset?: number): SelectQueryBuilder<Entity, RootAlias, Hint, Context>

• Parameters

  • optionallimit: number

  • offset: number = 0

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, Context>

merge

• merge(data?: Field<Entity>[] | EntityData<Entity>): this

• Parameters

  • optionaldata: Field<Entity>[] | EntityData<Entity>

  Returns this

offset

• offset(offset?: number): SelectQueryBuilder<Entity, RootAlias, Hint, Context>

• Parameters

  • optionaloffset: number

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, Context>

onConflict

• onConflict(fields?: Field<Entity> | Field<Entity>[]): InsertQueryBuilder<Entity>

• Parameters

  • fields: Field<Entity> | Field<Entity>[] = []

  Returns InsertQueryBuilder<Entity>

orderBy

• orderBy(orderBy: QBQueryOrderMap<Entity> | QBQueryOrderMap<Entity>[]): SelectQueryBuilder<Entity, RootAlias, Hint, Context>

• Parameters

  • orderBy: QBQueryOrderMap<Entity> | QBQueryOrderMap<Entity>[]

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, Context>

orHaving

• orHaving(cond?: string | QBFilterQuery, params?: any[]): SelectQueryBuilder<Entity, RootAlias, Hint, Context>

• Parameters

  • optionalcond: string | QBFilterQuery

  • optionalparams: any[]

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, Context>

orWhere

• orWhere(cond: QBFilterQuery<Entity>): this
• orWhere(cond: string, params?: any[]): this

• Parameters

  • cond: QBFilterQuery<Entity>

  Returns this

returning

• returning(fields?: Field<Entity> | Field<Entity>[]): this

• Parameters

  • optionalfields: Field<Entity> | Field<Entity>[]

  Returns this

select

• select(fields: Field<Entity> | Field<Entity>[], distinct?: boolean): SelectQueryBuilder<Entity, RootAlias, Hint, Context>

• Parameters

  • fields: Field<Entity> | Field<Entity>[]

  • distinct: boolean = false

  Returns SelectQueryBuilder<Entity, RootAlias, Hint, Context>

setFlag

• setFlag(flag: QueryFlag): this

• Parameters

  • flag: QueryFlag

  Returns this

setFlushMode

• setFlushMode(flushMode?: FlushMode): this

• Parameters

  • optionalflushMode: FlushMode

  Returns this

setLockMode

• setLockMode(mode?: LockMode, tables?: string[]): this

• Parameters

  • optionalmode: LockMode

  • optionaltables: string[]

  Returns this

setLoggerContext

• setLoggerContext(context: LoggingOptions & Dictionary): void

• Sets logger context for this query builder.

  ---

  Parameters

  • context: LoggingOptions & Dictionary

  Returns void

then

• then(onfulfilled?: null | (value: any) => TResult1 | PromiseLike<TResult1>, onrejected?: null | (reason: any) => TResult2 | PromiseLike<TResult2>): Promise<number | Loaded<Entity, Hint>[] | QueryResult<Entity>>

• Provides promise-like interface so we can await the QB instance.

  ---

  Parameters

  • optionalonfulfilled: null | (value: any) => TResult1 | PromiseLike<TResult1>

  • optionalonrejected: null | (reason: any) => TResult2 | PromiseLike<TResult2>

  Returns Promise<number | Loaded<Entity, Hint>[] | QueryResult<Entity>>

toQuery

• toQuery(): { _sql: Sql; params: readonly unknown[]; sql: string }

• Returns { _sql: Sql; params: readonly unknown[]; sql: string }

  • _sql: Sql

  • params: readonly unknown[]

  • sql: string

truncate

• truncate(): TruncateQueryBuilder<Entity>

• Returns TruncateQueryBuilder<Entity>

unsetFlag

• unsetFlag(flag: QueryFlag): this

• Parameters

  • flag: QueryFlag

  Returns this

update

• update(data: EntityData<Entity>): UpdateQueryBuilder<Entity>

• Parameters

  • data: EntityData<Entity>

  Returns UpdateQueryBuilder<Entity>

where

• where(cond: QBFilterQuery<Entity>, operator?: $and | $or): this
• where(cond: string, params?: any[], operator?: $and | $or): this

• Parameters

  • cond: QBFilterQuery<Entity>

  • optionaloperator: $and | $or

  Returns this

withSchema

• withSchema(schema?: string): this

• Parameters

  • optionalschema: string

  Returns this

withSubQuery

• withSubQuery(subQuery: QueryBuilder<any, any>, alias: string): this

• Parameters

  • subQuery: QueryBuilder<any, any>

  • alias: string

  Returns this