Skip to main content
Version: Next

QueryBuilder <T>

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

Index

Accessors

alias

  • get alias(): string
  • Returns string

helper

  • get helper(): QueryBuilderHelper
  • Returns QueryBuilderHelper

mainAlias

  • get mainAlias(): Alias<T>

Methods

addSelect

andWhere

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

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

  • Parameters

    • optionalreset: boolean | string[]

    Returns QueryBuilder<T>

comment

  • comment(comment: string | string[]): this
  • Prepend comment to the sql query using the syntax /* ... *&#8205;/. Some characters are forbidden such as /*, *&#8205;/ and ?.


    Parameters

    • comment: string | string[]

    Returns this

count

  • Parameters

    • optionalfield: string | string[]
    • distinct: boolean = false

    Returns CountQueryBuilder<T>

delete

distinct

distinctOn

execute

  • execute<U>(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).


    Type parameters

    • U = any

    Parameters

    • method: get | all | run = 'all'
    • optionaloptions: boolean | ExecuteOptions

    Returns Promise<U>

from

  • 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.


    Type parameters

    • T: Partial<T> = Partial<any>

    Parameters

    Returns SelectQueryBuilder<T>

getCount

  • getCount(field?: string | string[], distinct?: boolean): Promise<number>
  • Executes count query (without offset and limit), returning total count of results


    Parameters

    • optionalfield: string | string[]
    • 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>

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<T[]>
  • Alias for qb.getResultList()


    Returns Promise<T[]>

getResultAndCount

  • getResultAndCount(): Promise<[T[], number]>
  • Executes the query, returning both array of results and total count query (without offset and limit).


    Returns Promise<[T[], number]>

getResultList

  • getResultList(limit?: number): Promise<T[]>
  • Executes the query, returning array of results


    Parameters

    • optionallimit: number

    Returns Promise<T[]>

getSingleResult

  • getSingleResult(): Promise<null | T>
  • Executes the query, returning the first result or null


    Returns Promise<null | T>

groupBy

  • Parameters

    • fields: string | keyof T | readonly (string | keyof T)[]

    Returns SelectQueryBuilder<T>

hasFlag

having

hintComment

  • hintComment(comment: string | string[]): this
  • Add hints to the query using comment-like syntax /*+ ... *&#8205;/. 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: string | QueryBuilder<any, any> | QueryBuilder<any>, alias: string, cond?: QBFilterQuery, schema?: string): this
  • Parameters

    Returns this

innerJoinAndSelect

innerJoinLateral

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

    Returns this

innerJoinLateralAndSelect

insert

join

  • Parameters

    • field: string | QueryBuilder<any, any> | QueryBuilder<any>
    • alias: string
    • cond: QBFilterQuery = {}
    • type: JoinType = JoinType.innerJoin
    • optionalpath: string
    • optionalschema: string

    Returns this

joinAndSelect

  • Parameters

    • field: string | [field: string, qb: QueryBuilder<any, any> | QueryBuilder<any>]
    • alias: string
    • cond: QBFilterQuery = {}
    • type: JoinType = JoinType.innerJoin
    • optionalpath: string
    • optionalfields: string[]
    • optionalschema: string

    Returns SelectQueryBuilder<T>

leftJoin

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

    Returns this

leftJoinAndSelect

leftJoinLateral

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

    Returns this

leftJoinLateralAndSelect

limit

merge

offset

onConflict

orWhere

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

orderBy

returning

  • Parameters

    Returns this

select

setFlag

setFlushMode

  • Parameters

    Returns this

setLockMode

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

    • optionalmode: LockMode
    • optionaltables: string[]

    Returns this

then

  • then<TResult1, TResult2>(onfulfilled?: null | (value: any) => TResult1 | PromiseLike<TResult1>, onrejected?: null | (reason: any) => TResult2 | PromiseLike<TResult2>): Promise<number | T[] | QueryResult<T>>
  • Provides promise-like interface so we can await the QB instance.


    Type parameters

    • TResult1 = any
    • TResult2 = never

    Parameters

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

    Returns Promise<number | T[] | QueryResult<T>>

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

unsetFlag

update

where

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

    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