Class AggregateFunctionBuilder<DB, TB, O>

Simply calls the provided function passing this as the only argument. $call returns what the provided function returns.

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)
Copy

The generated SQL (PostgreSQL):

select count("id") as "person_count"
from "person"
Copy

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()
Copy

The generated SQL (PostgreSQL):

select count(distinct "first_name") as "first_name_count"
from "person"
Copy

Adds a filter clause with a nested where clause after the function.

Similar to WhereInterface's where method.

Also see 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()
Copy

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"
Copy

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()
Copy

The generated SQL (PostgreSQL):

select
  count("id") filter(where "first_name" = "last_name") as "repeat_name_count",
  count("id") as "total_count"
from "person"
Copy

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()
Copy

The generated SQL (PostgreSQL):

select avg("age") over() as "average_age"
from "person"
Copy

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()
Copy

The generated SQL (PostgreSQL):

select avg("age") over(partition by "last_name" order by "first_name" asc) as "average_age"
from "person"
Copy

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()
  }
}
Copy