Sub-Queries

When no operator expresses a condition, write it as a sql fragment. Its values are still bound as parameters. SQL databases only.

A raw condition in $where

Put it in a $and or $or array beside ordinary conditions, naming columns with refs():

You write

import { pool } from './uql.config.js';
import { sql, refs } from 'uql-orm';
import { Item } from './shared/models/index.js';

const item = refs(Item);
const items = await pool.findMany(Item, {
  $select: { id: true },
  $where: {
    $and: [{ companyId: 1 }, sql`${item.price} * ${item.stock} > ${10000}`],
  },
});

PostgreSQL

SELECT "id" FROM "Item" WHERE "companyId" = $1 AND "price" * "stock" > $2

$exists / $nexists sub-queries

Over a declared relation, use the built-in relation filters. Otherwise pass $exists or $nexists a sql callback, which receives the dialect to render a nested query:

You write

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

const items = await pool.findMany(Item, {
  $select: { id: true },
  $where: {
    $nexists: sql(({ ctx, dialect, escapedPrefix }) => {
      dialect.find(
        ctx,
        User,
        {
          $select: { id: true },
          $where: {
            // escapedPrefix is the outer table's alias, with its trailing dot.
            companyId: sql(({ ctx }) =>
              ctx.append(`${escapedPrefix}${dialect.escapeId('companyId')}`),
            ),
          },
        },
        { autoPrefix: true },
      );
    }),
  },
});

PostgreSQL

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

Capture the outer table’s escapedPrefix as above, and escape the column with dialect.escapeId: a refs() column inside the nested query would resolve against the nested table.

Understanding sql

sql has two forms. The template form, sql`${item.price} > ${limit}` , covers most SQL: values are bound and refs become columns. A sql value interpolated into another template is inserted as SQL, not bound, so fragments compose.

The callback form, sql(({ ctx, dialect, escapedPrefix }) => { ... }), is for SQL the dialect must render, such as a sub-query. The callback receives:

• ctx: the query being built. ctx.append(sql) writes SQL as is; ctx.addValue(value) binds a value and writes its placeholder.
• dialect: the current SQL dialect, for nested queries (dialect.find(...)).
• escapedPrefix: the escaped alias of the table in scope, with its trailing dot.
• prefix: the same alias, unescaped, and empty where there is none.

Instead of writing to ctx, the callback can return a string or number, which is appended as SQL. sql also works as a computed column in $select; see raw projections.

Next steps

• Raw SQL: the sql tag and running whole raw statements.
• Relations: built-in EXISTS and $size relation filters.
• Logical operators: combining raw conditions with others.