Zum Hauptinhalt springen

QueryBuilder

Class for building SQL Query

QueryBuilder

Deprecated​

in v23.0.0, Please use SelectBuilder

See​

SelectBuilder

Implements​

Constructors​

Constructor​

new QueryBuilder(from?, select?, where?, surroundWithBrackets?): QueryBuilder

Creates an instance of QueryBuilder.

Parameters​

from?​

string

(optional) the specified table to select

select?​

string[]

(optional) columns to select

where?​

string

(optional) a single where condition

surroundWithBrackets?​

boolean

(optional) Should surround names with brackets: column -> [column]

Returns​

QueryBuilder

Member Of​

QueryBuilder

Properties​

top​

top: number

Specified the max rows to select

Member Of​

QueryBuilder

distinct​

distinct: boolean

Select distinct values

Member Of​

QueryBuilder

toString()​

toString: () => string

Generates the SQL Query

Returns​

string

  • returns a sql query

Member Of​

QueryBuilder

Implementation of​

StatementBuilder.toString

Accessors​

isResultEditable​

Get Signature​

get isResultEditable(): boolean

Returns​

boolean

Methods​

select()​

select(columns, surroundWithBrackets?): QueryBuilder

Adds a column. You can also use an alias.

Example: select("column1"); or select("column1 as alias");

Parameters​

columns​

the name of the column to select

string | string[]

surroundWithBrackets?​

boolean

Should surround names with brackets: column -> [column]

Returns​

QueryBuilder

Member Of​

QueryBuilder

aggregate()​

aggregate(command, column, alias?, surroundWithBrackets?): QueryBuilder

Add a special command for a column like MAX, MIN, SUM, AVG

Parameters​

command​

string

SQL commands like MAX, MIN, SUM, AVG

column​

string

the specified column

alias?​

string

(optional) An alias for the command for example 'COUNT(column1) as total'

surroundWithBrackets?​

boolean

(optional) Should surround names with brackets: column -> [column]

Returns​

QueryBuilder

Member Of​

QueryBuilder

count()​

count(column, surroundWithBrackets?): QueryBuilder

Adds a count command for the specified column

Parameters​

column​

string

the column you want to count

surroundWithBrackets?​

boolean

(optional) Should surround names with brackets: column -> [column]

Returns​

QueryBuilder

Member Of​

QueryBuilder

from()​

from(table, surroundWithBrackets?): QueryBuilder

Specifies a table to select

Parameters​

table​

string

surroundWithBrackets?​

boolean

(optional) Should surround names with brackets: column -> [column]

Returns​

QueryBuilder

Member Of​

QueryBuilder

where()​

where(condition): QueryBuilder

Adds a where condition to the query

Parameters​

condition​

string

the where condition like "[Name] like 'Huber'"

Returns​

QueryBuilder

Member Of​

QueryBuilder

join()​

join(condition): QueryBuilder

Adds a join condition to the query

Parameters​

condition​

string

the join condition like "inner join [table2] on [table1].[column1] = [table2].[column1]"

Returns​

QueryBuilder

Member Of​

QueryBuilder

orderBy()​

orderBy(column, descending?, surroundWithBrackets?): QueryBuilder

Adds a order by condition to the query

Parameters​

column​

string

the column to sort

descending?​

boolean

(optional) sort direction descending, default value is false => ascending

surroundWithBrackets?​

boolean

(optional) Should surround names with brackets: column -> [column]

Returns​

QueryBuilder

Member Of​

QueryBuilder

groupBy()​

groupBy(columns, surroundWithBrackets?): QueryBuilder

Adds a group by condition to the query

Parameters​

columns​

the column or columns you want to group

string | string[]

surroundWithBrackets?​

boolean

(optional) Should surround names with brackets: column -> [column]

Returns​

QueryBuilder

Member Of​

QueryBuilder

having()​

having(condition): QueryBuilder

Adds a having condition to the query. The column should wrapped with brackets ([column] not column). Attention, you must provide a group by condition

Parameters​

condition​

string

The having condition statement

Returns​

QueryBuilder

See​

groupBy!

Example​

builder.having("count([column1]) > 10");

Member Of​

QueryBuilder

toSQL()​

toSQL(): string

Generates the SQL Query

Returns​

string

  • returns a sql query

Member Of​

QueryBuilder

Implementation of​

StatementBuilder.toSQL

getFromTables()​

getFromTables(): string[]

Returns​

string[]

from()​

static from(table, surroundWithBrackets?): QueryBuilder

Creates a new QueryBuilder instance with a specified table to select

Parameters​

table​

string

surroundWithBrackets?​

boolean

(optional) Should surround names with brackets: column -> [column]

Returns​

QueryBuilder

Member Of​

QueryBuilder

select()​

static select(...columns): QueryBuilder

Creates a new QueryBuilder instance with a range of columns to select

Parameters​

columns​

...string[]

Returns​

QueryBuilder

Member Of​

QueryBuilder

Examples​

const query = QueryBuilder.select("column1", "column2");
// Without brackets
const query = QueryBuilder.select("column1", false);