Sub-Queries

Sub-queries in UQL are written with raw expressions that interact directly with the QueryContext. They let you inject raw SQL fragments while still benefiting from UQL’s parameterization and dialect-aware engine.

Using raw in $where

The simplest use of a sub-query is adding a raw SQL condition to your $where clause.

You write

import { pool } from './uql.config.js';

import { raw } from 'uql-orm';
import { Item } from './shared/models/index.js';

const items = await pool.findMany(Item, {
  $select: { id: true },
  $where: {
    $and: [{ companyId: 1 }, raw`"salePrice" > "cost" * 2`],
  },
});

PostgreSQL

SELECT "id" FROM "Item" WHERE "companyId" = $1 AND "salePrice" > "cost" * 2

Advanced: Context-Aware Sub-Queries ($exists / $nexists)

For complex sub-queries like EXISTS or IN, you can pass a callback to raw. This callback provides access to the QueryContext and the dialect, allowing you to generate sub-queries that are correctly prefixed and compatible with your database. For EXISTS checks driven by entity relations, see the built-in relation filtering helpers.

You write

import { raw } from 'uql-orm';
import { User, Item } from './shared/models/index.js';

const items = await pool.findMany(Item, {
  $select: { id: true },
  $where: {
    $nexists: raw(({ ctx, dialect, escapedPrefix }) => {
      // Use the dialect to generate a nested SELECT statement
      dialect.find(
        ctx,
        User,
        {
          $select: { id: true },
          // Correlate on the OUTER alias, captured here. `col()` would resolve against the
          // inner statement's own prefix, which is not what a correlated sub-query wants.
          // `escapedPrefix` already ends with its dot.
          $where: {
            companyId: raw(({ ctx }) =>
              ctx.append(`${escapedPrefix}companyId`),
            ),
          },
        },
        { autoPrefix: true },
      );
    }),
  },
});

PostgreSQL

SELECT "id"
FROM "Item"
WHERE NOT EXISTS
    (SELECT "User"."id" FROM "User" WHERE "User"."companyId" = "Item"."companyId")

---

Understanding raw()

The raw() function from uql-orm injects SQL fragments into queries. It has two forms:

Form	Syntax	Use Case
Template	raw`"salePrice" > ${limit}`	Anything with a value in it. Interpolations are bound.
Callback	raw(({ ctx, dialect, escapedPrefix }) => { ... })	Complex sub-queries that need dialect-aware SQL generation.
String	raw('SQL fragment')	@deprecated: emits verbatim, so it cannot bind. Use the template.

An interpolated QueryRaw is emitted in place rather than bound, which is how fragments compose. The callback receives:

• ctx: the QueryContext for building parameterized SQL: ctx.append(sql) emits SQL as written, ctx.addValue(val) binds a value and emits its placeholder.
• dialect: the current SQL dialect instance for generating nested queries (e.g., dialect.find(...)).
• escapedPrefix: the escaped alias of the parent table, used to reference parent columns in correlated sub-queries.

Beyond $where, raw() also works as a computed $select projection (SQL dialects only) - see raw projections in $select.

---

Next Steps

• Raw SQL: The raw() helper and raw statement execution.
• Comparison Operators: The conditions a sub-query composes with.
• Relations: Built-in EXISTS/$size relation subqueries.
• Querier API: The full query API.