AggregateFunctionBuilder
§Implements
§Constructors
§Properties
All expressions need to have this getter for complicated type-related reasons.
Simply add this getter for your expression and always return undefined
from it:
class SomeExpression<T> implements Expression<T> {
get expressionType(): T | undefined {
return undefined
}
}
The getter is needed to make the expression assignable to another expression only
if the types T
are assignable. Without this property (or some other property
that references T
), you could assing Expression<string>
to Expression<number>
.
§Methods
Simply calls the provided function passing this
as the only argument. $call
returns
what the provided function returns.
Casts the expression to the given type.
This method call doesn't change the SQL in any way. This methods simply
returns a copy of this AggregateFunctionBuilder
with a new output type.
Omit null from the expression's type.
This function can be useful in cases where you know an expression can't be null, but Kysely is unable to infer it.
This method call doesn't change the SQL in any way. This methods simply
returns a copy of this
with a new output type.
Returns an aliased version of the function.
In addition to slapping as "the_alias"
to the end of the SQL,
this method also provides strict typing:
const result = await db
.selectFrom('person')
.select(
(eb) => eb.fn.count<number>('id').as('person_count')
)
.executeTakeFirstOrThrow()
// `person_count: number` field exists in the result type.
console.log(result.person_count)
The generated SQL (PostgreSQL):
select count("id") as "person_count"
from "person"
Adds a distinct
clause inside the function.
Examples
const result = await db
.selectFrom('person')
.select((eb) =>
eb.fn.count<number>('first_name').distinct().as('first_name_count')
)
.executeTakeFirstOrThrow()
The generated SQL (PostgreSQL):
select count(distinct "first_name") as "first_name_count"
from "person"
Adds a filter
clause with a nested where
clause after the function.
Similar to WhereInterface's where
method.
Also see {@link filterWhereRef}.
Examples
Count by gender:
const result = await db
.selectFrom('person')
.select((eb) => [
eb.fn
.count<number>('id')
.filterWhere('gender', '=', 'female')
.as('female_count'),
eb.fn
.count<number>('id')
.filterWhere('gender', '=', 'male')
.as('male_count'),
eb.fn
.count<number>('id')
.filterWhere('gender', '=', 'other')
.as('other_count'),
])
.executeTakeFirstOrThrow()
The generated SQL (PostgreSQL):
select
count("id") filter(where "gender" = $1) as "female_count",
count("id") filter(where "gender" = $2) as "male_count",
count("id") filter(where "gender" = $3) as "other_count"
from "person"
Adds a filter
clause with a nested where
clause after the function, where
both sides of the operator are references to columns.
Similar to WhereInterface's whereRef
method.
Examples
Count people with same first and last names versus general public:
const result = await db
.selectFrom('person')
.select((eb) => [
eb.fn
.count<number>('id')
.filterWhereRef('first_name', '=', 'last_name')
.as('repeat_name_count'),
eb.fn.count<number>('id').as('total_count'),
])
.executeTakeFirstOrThrow()
The generated SQL (PostgreSQL):
select
count("id") filter(where "first_name" = "last_name") as "repeat_name_count",
count("id") as "total_count"
from "person"
Adds an over
clause (window functions) after the function.
Examples
const result = await db
.selectFrom('person')
.select(
(eb) => eb.fn.avg<number>('age').over().as('average_age')
)
.execute()
The generated SQL (PostgreSQL):
select avg("age") over() as "average_age"
from "person"
Also supports passing a callback that returns an over builder, allowing to add partition by and sort by clauses inside over.
const result = await db
.selectFrom('person')
.select(
(eb) => eb.fn.avg<number>('age').over(
ob => ob.partitionBy('last_name').orderBy('first_name', 'asc')
).as('average_age')
)
.execute()
The generated SQL (PostgreSQL):
select avg("age") over(partition by "last_name" order by "first_name" asc) as "average_age"
from "person"
Creates the OperationNode that describes how to compile this expression into SQL.
If you are creating a custom expression, it's often easiest to use the sql template tag to build the node:
class SomeExpression<T> implements Expression<T> {
toOperationNode(): OperationNode {
return sql`some sql here`.toOperationNode()
}
}