Query Builder: Joins & Subqueries

Holo-JS supports the common join patterns you expect from a fluent builder, but compilation stays explicit and validated per dialect.

Basic joins

const rows = await DB.table('users as u')
  .join('profiles', 'profiles.user_id', '=', 'u.id')
  .leftJoin('teams', 'teams.id', '=', 'u.team_id')
  .get()

Supported join families include:

• join
• leftJoin
• rightJoin
• crossJoin

Use joins when the result should remain a table-shaped projection. If the application really wants related entities and lifecycle behavior, model relations are usually the better fit.

Typed joins

Pass generated table definitions to the table builder and joins to retain column inference:

const rows = await DB.table(members)
  .join(users, 'members.userId', '=', 'users.id')
  .select('members.id', 'users.name as userName')
  .get()

For a numeric member ID and string user name, rows is inferred as { id: number; userName: string }[]. Qualified columns and selection aliases retain their column types. Filters and ordering accept columns from the joined definitions.

leftJoin() adds null to columns from the joined table. rightJoin() adds null to columns from the preceding tables. crossJoin() also accepts a table definition. Use explicit selections and distinct aliases when column names overlap.

String table names and subquery joins remain available, but their joined projections do not carry schema inference. Typed joins use the same SQL compilation and locking behavior.

Subquery joins

const latestPosts = DB.table('posts')
  .select('user_id', 'title')
  .orderBy('created_at', 'desc')

const rows = await DB.table('users')
  .joinSub(latestPosts, 'latest_posts', 'latest_posts.user_id', '=', 'users.id')
  .get()

Subquery joins require explicit aliases. The compiler rejects missing aliases instead of guessing.

Use a subquery join when the joined shape needs its own filtered or aggregated query plan first.

Lateral joins

const latestPost = DB.table('posts')
  .select('title')
  .whereColumn('posts.user_id', '=', 'users.id')
  .limit(1)

const rows = await DB.table('users')
  .joinLateral(latestPost, 'latest_post')
  .get()

Lateral joins only compile on supporting dialects. Unsupported dialects fail closed.

Use lateral joins only when the query genuinely needs row-by-row dependent subqueries.

Scalar subqueries

const rows = await DB.table('users')
  .whereSub(
    'score',
    '>',
    DB.table('scores').select('value').whereColumn('scores.user_id', '=', 'users.id').limit(1)
  )
  .get()

Use scalar subqueries when one column should be compared against the result of another focused query.

Set subqueries

const activeUserIds = DB.table('sessions')
  .select('user_id')
  .where('active', true)

const users = await DB.table('users')
  .whereInSub('id', activeUserIds)
  .get()

Use set subqueries when the membership test belongs in SQL and should stay close to the database.

Unions

const first = DB.table('users').select('email')
const second = DB.table('admins').select('email')

const emails = await first.unionAll(second).get()

Full text and advanced clauses

const docs = await DB.table('documents')
  .whereFullText(['title', 'body'], 'compiler')
  .get()

The query compiler decides whether a clause is supported. The adapter does not try to emulate missing SQL syntax.