Guides / Select

Add optional conditions

Add or omit conditions at runtime, and handle NULL values and empty lists.

The examples use the users table from Build a SELECT.

Omit query parts conditionally

Use omit as the other branch of a JavaScript conditional when a WHERE, HAVING, ORDER BY, or DISTINCT clause is optional:

import { eq, from, omit, select, where } from "qubu"

declare const userId: number | undefined

const query = select(
  { id: users.id, name: users.name },
  from(users),
  userId === undefined ? omit : where(eq(users.id, userId)),
)

The ordinary ternary narrows userId, and the unused clause is never built. Qubu removes omit before validating and ordering the remaining clauses.

Use the same token inside and(), or(), and orderBy() when individual predicates or ordering terms are conditional:

import { and, desc, eq, omit, orderBy, where } from "qubu"

declare const includeName: boolean
declare const newestFirst: boolean

const filter = where(and(eq(users.id, 7), includeName ? eq(users.name, "Ada") : omit))
const ordering = orderBy(newestFirst ? desc(users.name) : omit)

Each helper removes omitted members while retaining the source and grouping requirements of every member that may be present. If no predicate remains, and() or or() propagates omit through where() or having(); if no ordering term remains, orderBy() propagates omit directly. The resulting query emits no empty clause.

The same token can conditionally include a projection field:

declare const includeEmail: boolean

const query = select(
  {
    id: users.id,
    email: includeEmail ? users.email : omit,
  },
  from(users),
)

// typeof query.row:
// { id: number; email?: string | null }

Here omit affects only whether email belongs to the projection. It does not make the expression nullable: a non-nullable expression would produce email?: string, while this nullable column produces email?: string | null.

Where omission is supported

Use omit in the clauses and expression lists shown above. Generic sequence() and commaSeparated() collections do not discard it.

Pagination also supports omit: pair it with offset(), fetchFirst(), or fetchNext(). A conditional limit keeps the inferred cardinality at many because the limit may be absent.

Build separate queries when any of these parts differ at runtime. They cannot be paired with omit:

  • from() or joins.
  • groupBy().
  • Correlation.
  • CTEs.
  • Custom clauses.

Handle NULL and empty lists deliberately

Equality with null is translated to the SQL null predicate:

import { eq, isDistinctFrom, ne } from "qubu"

eq(users.name, null) // ... IS NULL
ne(users.name, null) // ... IS NOT NULL
isDistinctFrom(users.name, null) // ... IS DISTINCT FROM ?

Relational comparisons such as gt(users.id, null) are rejected because SQL does not give them ordinary boolean comparison semantics. Use isNull, isNotNull, or a distinctness predicate when that is the intended operation.

Empty membership lists remain valid and portable:

inList(users.id, []) // (1 = 0)
notIn(users.id, []) // (1 = 1)