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"
Clears the order by clause from the query.
See orderBy for adding an order by clause or item to a query.
const query = db
.selectFrom('person')
.selectAll()
.orderBy('id', 'desc')
const results = await query
.clearOrderBy()
.execute()
The generated SQL (PostgreSQL):
select * from "person"
Adds a distinct clause inside the function.
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 filterWhereRef.
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.
Similar to WhereInterface's where method.
Also see filterWhereRef.
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.
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 order by clause inside the aggregate function.
const result = await db
.selectFrom('person')
.innerJoin('pet', 'pet.owner_id', 'person.id')
.select((eb) =>
eb.fn.jsonAgg('pet').orderBy('pet.name').as('person_pets')
)
.executeTakeFirstOrThrow()
The generated SQL (PostgreSQL):
select json_agg("pet" order by "pet"."name") as "person_pets"
from "person"
inner join "pet" ON "pet"."owner_id" = "person"."id"
Optionalmodifiers: OrderByModifiersAdds an over clause (window functions) after the function.
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"
Optionalover: OverBuilderCallback<DB, TB>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:
import { type Expression, type OperationNode, sql } from 'kysely'
class SomeExpression<T> implements Expression<T> {
get expressionType(): T | undefined {
return undefined
}
toOperationNode(): OperationNode {
return sql`some sql here`.toOperationNode()
}
}
Adds a withing group clause with a nested order by clause after the function.
This is only supported by some dialects like PostgreSQL or MS SQL Server.
Most frequent person name:
const result = await db
.selectFrom('person')
.select((eb) => [
eb.fn
.agg<string>('mode')
.withinGroupOrderBy('person.first_name')
.as('most_frequent_name')
])
.executeTakeFirstOrThrow()
The generated SQL (PostgreSQL):
select mode() within group (order by "person"."first_name") as "most_frequent_name"
from "person"
Optionalmodifiers: OrderByModifiers
An expression with an
asmethod.