Select API¶
This API defines how to construct SQL SELECT
statements using a fluent interface in ts-sql-query
. It supports filtering, ordering, pagination, grouping, and joining tables. It also provides support for compound and recursive queries, as well as integration with advanced features like Oracle's CONNECT BY
or database-specific query customizations.
The select query definition must follow the logical order or the alternative order:
- Logical order: from, join, WHERE, group by, having, select, order by, limit, offset, customizeQuery
- Alternative logical order 1: from, join, group by, having, WHERE, select, order by, limit, offset, customizeQuery
- Arternative logical order 2: from, join, group by, having, select, WHERE, order by, limit, offset, customizeQuery
- Arternative logical order 3: from, join, group by, having, select, order by, WHERE, limit, offset, customizeQuery
- Arternative logical order 4: from, join, group by, having, select, order by, limit, offset, WHERE, customizeQuery
- Arternative logical order 5: from, join, group by, having, select, order by, limit, offset, customizeQuery, WHERE
- Alternative order 1: from, join, select, WHERE, group by, having, order by, limit, offset, customizeQuery
- Alternative order 2: from, join, select, group by, having, WHERE, order by, limit, offset, customizeQuery
- Alternative order 3: from, join, select, group by, having, order by, WHERE, limit, offset, customizeQuery
- Alternative order 4: from, join, select, group by, having, order by, limit, offset, WHERE, customizeQuery
- Alternative order 5: from, join, select, group by, having, order by, limit, offset, customizeQuery, WHERE
interface SelectExpression {
/** Allows to add a from to the select query */
from(table: Table | View): this
/** Allows to add a join to the select query */
join(table: Table | View): this
/** Allows to add a inner join to the select query */
innerJoin(table: Table | View): this
/**
* Allows to add a left join to the select query.
* Note: to use a table or view here you must call first forUseInLeftJoin methods on it
*/
leftJoin(source: OuterJoinSource): this
/**
* Allows to add a left outer join to the select query.
* Note: to use a table or view here you must call first forUseInLeftJoin methods on it
*/
leftOuterJoin(source: OuterJoinSource): this
// Optional variants for joins, to be used in select picking columns
optionalJoin(table: Table | View): this
optionalInnerJoin(table: Table | View): this
optionalLeftJoin(source: OuterJoinSource): this
optionalLeftOuterJoin(source: OuterJoinSource): this
/** Allows to create the on clause of a join dynamically */
dynamicOn(): this
/** Allows to specify the on clause of a join */
on(condition: BooleanValueSource): this
/** Allows to create the where dynamically */
dynamicWhere(): this
/** Allows to specify the where */
where(condition: BooleanValueSource): this
/** Allows to specify the group by of the select query */
groupBy(...columns: AnyValueSource[]): this
/**
* Allows to specify the group by of the select query.
*
* If you already defined the select clause, you can use the name of
* the properties returned by the select instead of its definition, it
* will be replace by the definition automatically.
*
* Note: this overload is only available if you define the select clause first.
*/
groupBy(...columns: string[]): this
/** Allows to create the having clause of the group by dynamically */
dynamicHaving(): this
/** Allows to specify the having clause of the group by */
having(condition: BooleanValueSource): this
/**
* Allows to specify the select clause.
* It must be an object where the name of the property is the name of the resulting property
* and the value is the ValueSource where the value will be obtained.
*/
select(columns: SelectValues): this
/** Returns the optional values as null instead of optional undefined values, can only used immediately after select(...) */
projectingOptionalValuesAsNullable(): this
/**
* Allows to specify the select clause of a query that returns only one column.
* It receives as argument the ValueSource where the value will be obtained.
*/
selectOneColumn(column: AnyValueSource): this
/**
* Allows to specify the select clause of a query that returns only one column with count(*).
*/
selectCountAll(): this
/**
* Allows to specify an order by used by the query, you must indicate the name of the column
* returned by the query.
* If you select one column the name of the column is 'result'.
*/
orderBy(column: string, mode?: OrderByMode): this
orderBy(column: AnyValueSource, mode?: OrderByMode): this
orderBy(column: RawFragment, mode?: OrderByMode): this
/** Allows to specify an order by dynamically, it is parsed from the provided string */
orderByFromString(orderBy: string): this
orderByFromStringIfValue(orderBy: string | null | undefined): this
/** Allows to specify the maximum number of rows that will be returned by the query */
limit(limit: number): this
limitIfValue(limit: number | null | undefined): this
/** Allows to specify the number of first rows ignored by the query */
offset(offset: number): this
offsetIfValue(offset: number | null | undefined): this
// Oracle's connect by syntax
startWith(condition: BooleanValueSource): this
connectBy(condition: (prior: (column: AnyValueSource) => AnyValueSource) => BooleanValueSource): this
connectByNoCycle(condition: (prior: (column: AnyValueSource) => AnyValueSource) => BooleanValueSource): this
orderingSiblingsOnly(): this
/** Allows to extends the where, or the on clause of a join, or the having clause using an and */
and(condition: BooleanValueSource): this
/** Allows to extends the where, or the on clause of a join, or the having clause using an or */
or(condition: BooleanValueSource): this
// Query compound operators
union(select: CompoundableSubquery): this
unionAll(select: CompoundableSubquery): this
intersect(select: CompoundableSubquery): this
intersectAll(select: CompoundableSubquery): this
except(select: CompoundableSubquery): this
exceptAll(select: CompoundableSubquery): this
minus(select: CompoundableSubquery): this // alias to except
minusAll(select: CompoundableSubquery): this // alias to exceptAll
// Recursive queries
recursiveUnion(fn: (view: View) => CompoundableSubquery): this
recursiveUnionAll(fn: (view: View) => CompoundableSubquery): this
recursiveUnionOn(fn: (view: View) => BooleanValueSource): this
recursiveUnionAllOn(fn: (view: View) => BooleanValueSource): this
/** Execute the select query that returns one o no result from the database */
executeSelectNoneOrOne(): Promise<RESULT | null>
/**
* Execute the select query that returns one result from the database.
* If no result is returned by the database an exception will be thrown.
*/
executeSelectOne(): Promise<RESULT>
/** Execute the select query that returns zero or many results from the database */
executeSelectMany(): Promise<RESULT[]>
/**
* Execute the select query that returns zero or many results from the database.
* Select page execute the query twice, the first one to get the data from the database
* and the second one to get the count of all data without the limit and the offset.
* Note: select page is only available if you don't define a group by clause.
*/
executeSelectPage(): Promise<{ data: RESULT[], count: number }>
/**
* Execute the select query as a select page, but allows to include extra properties to will be resulting object.
* If the object provided by argument includes the property count, the query that count the data will be omitted and
* this value will be used. If the object provided by argument includes the property data, the query that extract
* the data will be omitted and this value will be used.
*/
executeSelectPage<EXTRAS extends {}>(extras: EXTRAS): Promise<{ data: RESULT[], count: number } & EXTRAS>
/**
* Allows to use a select query as a view in another select.
* This select will be included as a clause with in the final sql.
*
* @param as name of the clause with in the final query (must be unique per final query)
*/
forUseInQueryAs(as: string): View
/**
* Allows to use a select query as an inline query value in another select.
*/
forUseAsInlineQueryValue(): AnyValueSource
/**
* Allows to use a select query as an inline object array value in another select.
*/
forUseAsInlineAggregatedArrayValue(): AggregatedArrayValueSource<this>
/** Returns the sql query to be executed in the database */
query(): string
/** Returns the required parameters by the sql query */
params(): any[]
customizeQuery(customization: {
afterSelectKeyword?: RawFragment
beforeColumns?: RawFragment
customWindow?: RawFragment
afterQuery?: RawFragment
beforeWithQuery?: RawFragment
afterWithQuery?: RawFragment
queryExecutionName?: string
queryExecutionMetadata?: any
}): this
}
/**
* Modes of sorting in an order by.
* If the database don't support one of then it will be emulated.
*/
type OrderByMode = 'asc' | 'desc' | 'asc nulls first' | 'asc nulls last' | 'desc nulls first' | 'desc nulls last' | 'insensitive' |
'asc insensitive' | 'desc insensitive' | 'asc nulls first insensitive' | 'asc nulls last insensitive' |
'desc nulls first insensitive' | 'desc nulls last insensitive'
/**
* Select projection of the value that vill be retreived from the database.
*
* It must be an object where the name of the property is the name of the resulting property
* and the value is the ValueSource where the value will be obtained.
*/
type SelectValues = { [columnName: string]: AnyValueSource }