Class: BaseQueryBuilder<TContext>

Defined in: packages/db/src/query/builder/index.ts:46

Constructors

Constructor

new BaseQueryBuilder<TContext>(query): BaseQueryBuilder<TContext>;

new BaseQueryBuilder<TContext>(query): BaseQueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:49

Accessors

fn

Get Signature

get fn(): object;

get fn(): object;

Defined in: packages/db/src/query/builder/index.ts:672

Functional variants of the query builder These are imperative function that are called for ery row. Warning: that these cannot be optimized by the query compiler, and may prevent some type of optimizations being possible.

Example

q.fn.select((row) => ({
  name: row.user.name.toUpperCase(),
  age: row.user.age + 1,
}))

q.fn.select((row) => ({
  name: row.user.name.toUpperCase(),
  age: row.user.age + 1,
}))

Returns

having()

having(callback): QueryBuilder<TContext>;

having(callback): QueryBuilder<TContext>;

Filter grouped rows using a function that operates on each aggregated row Warning: This cannot be optimized by the query compiler

Parameters

callback

(row) => any

A function that receives an aggregated row and returns a boolean

Returns

QueryBuilder<TContext>

A QueryBuilder with functional having filter applied

Example

// Functional having (not optimized)
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .fn.having(row => row.count > 5)

// Functional having (not optimized)
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .fn.having(row => row.count > 5)

select()

select<TFuncSelectResult>(callback): QueryBuilder<WithResult<TContext, TFuncSelectResult>>;

select<TFuncSelectResult>(callback): QueryBuilder<WithResult<TContext, TFuncSelectResult>>;

Select fields using a function that operates on each row Warning: This cannot be optimized by the query compiler

Type Parameters

TFuncSelectResult

Parameters

callback

(row) => TFuncSelectResult

A function that receives a row and returns the selected value

Returns

QueryBuilder<WithResult<TContext, TFuncSelectResult>>

A QueryBuilder with functional selection applied

Example

// Functional select (not optimized)
query
  .from({ users: usersCollection })
  .fn.select(row => ({
    name: row.users.name.toUpperCase(),
    age: row.users.age + 1,
  }))

// Functional select (not optimized)
query
  .from({ users: usersCollection })
  .fn.select(row => ({
    name: row.users.name.toUpperCase(),
    age: row.users.age + 1,
  }))

where()

where(callback): QueryBuilder<TContext>;

where(callback): QueryBuilder<TContext>;

Filter rows using a function that operates on each row Warning: This cannot be optimized by the query compiler

Parameters

callback

(row) => any

A function that receives a row and returns a boolean

Returns

QueryBuilder<TContext>

A QueryBuilder with functional filtering applied

Example

// Functional where (not optimized)
query
  .from({ users: usersCollection })
  .fn.where(row => row.users.name.startsWith('A'))

// Functional where (not optimized)
query
  .from({ users: usersCollection })
  .fn.where(row => row.users.name.startsWith('A'))

Methods

_getQuery()

_getQuery(): QueryIR;

_getQuery(): QueryIR;

Defined in: packages/db/src/query/builder/index.ts:758

Returns

QueryIR

distinct()

distinct(): QueryBuilder<TContext>;

distinct(): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:611

Specify that the query should return distinct rows. Deduplicates rows based on the selected columns.

Returns

QueryBuilder<TContext>

A QueryBuilder with distinct enabled

Example

// Get countries our users are from
query
  .from({ users: usersCollection })
  .select(({users}) => users.country)
  .distinct()

// Get countries our users are from
query
  .from({ users: usersCollection })
  .select(({users}) => users.country)
  .distinct()

findOne()

findOne(): QueryBuilder<TContext & SingleResult>;

findOne(): QueryBuilder<TContext & SingleResult>;

Defined in: packages/db/src/query/builder/index.ts:631

Specify that the query should return a single result

Returns

QueryBuilder<TContext & SingleResult>

A QueryBuilder that returns the first result

Example

// Get the user matching the query
query
  .from({ users: usersCollection })
  .where(({users}) => eq(users.id, 1))
  .findOne()

// Get the user matching the query
query
  .from({ users: usersCollection })
  .where(({users}) => eq(users.id, 1))
  .findOne()

from()

from<TSource>(source): QueryBuilder<{
  baseSchema: SchemaFromSource<TSource>;
  fromSourceName: keyof TSource & string;
  hasJoins: false;
  schema: SchemaFromSource<TSource>;
}>;

from<TSource>(source): QueryBuilder<{
  baseSchema: SchemaFromSource<TSource>;
  fromSourceName: keyof TSource & string;
  hasJoins: false;
  schema: SchemaFromSource<TSource>;
}>;

Defined in: packages/db/src/query/builder/index.ts:103

Specify the source table or subquery for the query

Type Parameters

TSource

TSource extends Source

Parameters

source

TSource

An object with a single key-value pair where the key is the table alias and the value is a Collection or subquery

Returns

QueryBuilder<{ baseSchema: SchemaFromSource<TSource>; fromSourceName: keyof TSource & string; hasJoins: false; schema: SchemaFromSource<TSource>; }>

A QueryBuilder with the specified source

Example

// Query from a collection
query.from({ users: usersCollection })

// Query from a subquery
const activeUsers = query.from({ u: usersCollection }).where(({u}) => u.active)
query.from({ activeUsers })

// Query from a collection
query.from({ users: usersCollection })

// Query from a subquery
const activeUsers = query.from({ u: usersCollection }).where(({u}) => u.active)
query.from({ activeUsers })

fullJoin()

fullJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "full">>;

fullJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "full">>;

Defined in: packages/db/src/query/builder/index.ts:294

Perform a FULL JOIN with another table or subquery

Type Parameters

TSource

TSource extends Source

Parameters

source

TSource

An object with a single key-value pair where the key is the table alias and the value is a Collection or subquery

onCallback

JoinOnCallback<MergeContextForJoinCallback<TContext, { [K in string | number | symbol]: { [K in string | number | symbol]: TSource[K] extends CollectionImpl<any, any, any, any, any> ? InferCollectionType<any[any]> : TSource[K] extends QueryBuilder<TContext> ? { [K in string | number | symbol]: ((...)[(...)] extends object ? any[any] : (...) extends (...) ? (...) : (...))[K] } : never }[K] }>>

A function that receives table references and returns the join condition

Returns

QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "full">>

A QueryBuilder with the full joined table available

Example

// Full join users with posts
query
  .from({ users: usersCollection })
  .fullJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

// Full join users with posts
query
  .from({ users: usersCollection })
  .fullJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

groupBy()

groupBy(callback): QueryBuilder<TContext>;

groupBy(callback): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:533

Group rows by one or more columns for aggregation

Parameters

callback

GroupByCallback<TContext>

A function that receives table references and returns the field(s) to group by

Returns

QueryBuilder<TContext>

A QueryBuilder with grouping applied (enables aggregate functions in SELECT and HAVING)

Example

// Group by a single column
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .select(({posts, count}) => ({
    userId: posts.userId,
    postCount: count()
  }))

// Group by multiple columns
query
  .from({ sales: salesCollection })
  .groupBy(({sales}) => [sales.region, sales.category])
  .select(({sales, sum}) => ({
    region: sales.region,
    category: sales.category,
    totalSales: sum(sales.amount)
  }))

// Group by a single column
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .select(({posts, count}) => ({
    userId: posts.userId,
    postCount: count()
  }))

// Group by multiple columns
query
  .from({ sales: salesCollection })
  .groupBy(({sales}) => [sales.region, sales.category])
  .select(({sales, sum}) => ({
    region: sales.region,
    category: sales.category,
    totalSales: sum(sales.amount)
  }))

having()

having(callback): QueryBuilder<TContext>;

having(callback): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:374

Filter grouped rows based on aggregate conditions

Parameters

callback

WhereCallback<TContext>

A function that receives table references and returns an expression

Returns

QueryBuilder<TContext>

A QueryBuilder with the having condition applied

Example

// Filter groups by count
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .having(({posts}) => gt(count(posts.id), 5))

// Filter by average
query
  .from({ orders: ordersCollection })
  .groupBy(({orders}) => orders.customerId)
  .having(({orders}) => gt(avg(orders.total), 100))

// Multiple having calls are ANDed together
query
  .from({ orders: ordersCollection })
  .groupBy(({orders}) => orders.customerId)
  .having(({orders}) => gt(count(orders.id), 5))
  .having(({orders}) => gt(avg(orders.total), 100))

// Filter groups by count
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .having(({posts}) => gt(count(posts.id), 5))

// Filter by average
query
  .from({ orders: ordersCollection })
  .groupBy(({orders}) => orders.customerId)
  .having(({orders}) => gt(avg(orders.total), 100))

// Multiple having calls are ANDed together
query
  .from({ orders: ordersCollection })
  .groupBy(({orders}) => orders.customerId)
  .having(({orders}) => gt(count(orders.id), 5))
  .having(({orders}) => gt(avg(orders.total), 100))

innerJoin()

innerJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "inner">>;

innerJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "inner">>;

Defined in: packages/db/src/query/builder/index.ts:268

Perform an INNER JOIN with another table or subquery

Type Parameters

TSource

TSource extends Source

Parameters

source

TSource

An object with a single key-value pair where the key is the table alias and the value is a Collection or subquery

onCallback

A function that receives table references and returns the join condition

Returns

QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "inner">>

A QueryBuilder with the inner joined table available

Example

// Inner join users with posts
query
  .from({ users: usersCollection })
  .innerJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

// Inner join users with posts
query
  .from({ users: usersCollection })
  .innerJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

join()

join<TSource, TJoinType>(
   source, 
   onCallback, 
type): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, TJoinType>>;

join<TSource, TJoinType>(
   source, 
   onCallback, 
type): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, TJoinType>>;

Defined in: packages/db/src/query/builder/index.ts:146

Join another table or subquery to the current query

Type Parameters

TSource

TSource extends Source

TJoinType

TJoinType extends "inner" | "left" | "right" | "full" = "left"

Parameters

source

TSource

An object with a single key-value pair where the key is the table alias and the value is a Collection or subquery

onCallback

A function that receives table references and returns the join condition

type

TJoinType = ...

The type of join: 'inner', 'left', 'right', or 'full' (defaults to 'left')

Returns

QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, TJoinType>>

A QueryBuilder with the joined table available

Example

// Left join users with posts
query
  .from({ users: usersCollection })
  .join({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

// Inner join with explicit type
query
  .from({ u: usersCollection })
  .join({ p: postsCollection }, ({u, p}) => eq(u.id, p.userId), 'inner')

// Left join users with posts
query
  .from({ users: usersCollection })
  .join({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

// Inner join with explicit type
query
  .from({ u: usersCollection })
  .join({ p: postsCollection }, ({u, p}) => eq(u.id, p.userId), 'inner')

// Join with a subquery const activeUsers = query.from({ u: usersCollection }).where(({u}) => u.active) query .from({ activeUsers }) .join({ p: postsCollection }, ({u, p}) => eq(u.id, p.userId))

leftJoin()

leftJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "left">>;

leftJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "left">>;

Defined in: packages/db/src/query/builder/index.ts:216

Perform a LEFT JOIN with another table or subquery

// Left join users with posts
query
  .from({ users: usersCollection })
  .leftJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

// Left join users with posts
query
  .from({ users: usersCollection })
  .leftJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

limit()

limit(count): QueryBuilder<TContext>;

limit(count): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:566

Limit the number of rows returned by the query orderBy is required for limit

// Get top 5 posts by likes
query
  .from({ posts: postsCollection })
  .orderBy(({posts}) => posts.likes, 'desc')
  .limit(5)

// Get top 5 posts by likes
query
  .from({ posts: postsCollection })
  .orderBy(({posts}) => posts.likes, 'desc')
  .limit(5)

offset()

offset(count): QueryBuilder<TContext>;

offset(count): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:590

Skip a number of rows before returning results orderBy is required for offset

// Get second page of results
query
  .from({ posts: postsCollection })
  .orderBy(({posts}) => posts.createdAt, 'desc')
  .offset(page * pageSize)
  .limit(pageSize)

// Get second page of results
query
  .from({ posts: postsCollection })
  .orderBy(({posts}) => posts.createdAt, 'desc')
  .offset(page * pageSize)
  .limit(pageSize)

orderBy()

orderBy(callback, options): QueryBuilder<TContext>;

orderBy(callback, options): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:462

Sort the query results by one or more columns

// Sort by a single column
query
  .from({ users: usersCollection })
  .orderBy(({users}) => users.name)

// Sort descending
query
  .from({ users: usersCollection })
  .orderBy(({users}) => users.createdAt, 'desc')

// Multiple sorts (chain orderBy calls)
query
  .from({ users: usersCollection })
  .orderBy(({users}) => users.lastName)
  .orderBy(({users}) => users.firstName)

// Sort by a single column
query
  .from({ users: usersCollection })
  .orderBy(({users}) => users.name)

// Sort descending
query
  .from({ users: usersCollection })
  .orderBy(({users}) => users.createdAt, 'desc')

// Multiple sorts (chain orderBy calls)
query
  .from({ users: usersCollection })
  .orderBy(({users}) => users.lastName)
  .orderBy(({users}) => users.firstName)

rightJoin()

rightJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "right">>;

rightJoin<TSource>(source, onCallback): QueryBuilder<MergeContextWithJoinType<TContext, SchemaFromSource<TSource>, "right">>;

Defined in: packages/db/src/query/builder/index.ts:242

Perform a RIGHT JOIN with another table or subquery

// Right join users with posts
query
  .from({ users: usersCollection })
  .rightJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

// Right join users with posts
query
  .from({ users: usersCollection })
  .rightJoin({ posts: postsCollection }, ({users, posts}) => eq(users.id, posts.userId))

select()

select<TSelectObject>(callback): QueryBuilder<WithResult<TContext, ResultTypeFromSelect<TSelectObject>>>;

select<TSelectObject>(callback): QueryBuilder<WithResult<TContext, ResultTypeFromSelect<TSelectObject>>>;

Defined in: packages/db/src/query/builder/index.ts:421

Select specific columns or computed values from the query

// Select specific columns
query
  .from({ users: usersCollection })
  .select(({users}) => ({
    name: users.name,
    email: users.email
  }))

// Select with computed values
query
  .from({ users: usersCollection })
  .select(({users}) => ({
    fullName: concat(users.firstName, ' ', users.lastName),
    ageInMonths: mul(users.age, 12)
  }))

// Select with aggregates (requires GROUP BY)
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .select(({posts, count}) => ({
    userId: posts.userId,
    postCount: count(posts.id)
  }))

// Select specific columns
query
  .from({ users: usersCollection })
  .select(({users}) => ({
    name: users.name,
    email: users.email
  }))

// Select with computed values
query
  .from({ users: usersCollection })
  .select(({users}) => ({
    fullName: concat(users.firstName, ' ', users.lastName),
    ageInMonths: mul(users.age, 12)
  }))

// Select with aggregates (requires GROUP BY)
query
  .from({ posts: postsCollection })
  .groupBy(({posts}) => posts.userId)
  .select(({posts, count}) => ({
    userId: posts.userId,
    postCount: count(posts.id)
  }))

where()

where(callback): QueryBuilder<TContext>;

where(callback): QueryBuilder<TContext>;

Defined in: packages/db/src/query/builder/index.ts:333

Filter rows based on a condition

// Simple condition
query
  .from({ users: usersCollection })
  .where(({users}) => gt(users.age, 18))

// Multiple conditions
query
  .from({ users: usersCollection })
  .where(({users}) => and(
    gt(users.age, 18),
    eq(users.active, true)
  ))

// Multiple where calls are ANDed together
query
  .from({ users: usersCollection })
  .where(({users}) => gt(users.age, 18))
  .where(({users}) => eq(users.active, true))

// Simple condition
query
  .from({ users: usersCollection })
  .where(({users}) => gt(users.age, 18))

// Multiple conditions
query
  .from({ users: usersCollection })
  .where(({users}) => and(
    gt(users.age, 18),
    eq(users.active, true)
  ))

// Multiple where calls are ANDed together
query
  .from({ users: usersCollection })
  .where(({users}) => gt(users.age, 18))
  .where(({users}) => eq(users.active, true))