Subqueries

SlintORM supports correlated subqueries in SELECT, EXISTS/NOT EXISTS in WHERE, and UNION / UNION ALL across multiple queries. All via model.advanced().

selectSubquery

Adds a raw SQL subquery to the SELECT clause with an alias. Useful for inline counts or lookups.

// .selectSubquery(subquery, alias)
// Adds a correlated subquery in the SELECT clause

// Count comments per post inline
const posts = await Post.advanced()
  .select('id', 'title', 'userId')
  .selectSubquery(
    'SELECT COUNT(*) FROM comments WHERE comments.postId = posts.id',
    'commentCount'
  )
  .get();

// posts[0].commentCount — comment count for that post

exists

// .exists(subquery) — WHERE EXISTS (subquery)
// Returns only rows where the subquery returns at least one row

const usersWithPosts = await User.advanced()
  .exists('SELECT 1 FROM posts WHERE posts.userId = users.id')
  .get();
// Only users who have written at least one post

notExists

// .notExists(subquery) — WHERE NOT EXISTS (subquery)
const usersWithoutPosts = await User.advanced()
  .notExists('SELECT 1 FROM posts WHERE posts.userId = users.id')
  .get();
// Users who have never written a post

whereRaw for IN subqueries

// Subquery inside whereRaw with params
// Useful for IN-subquery patterns

const popularPostAuthors = await User.query()
  .whereRaw(
    'id IN (SELECT userId FROM posts WHERE viewCount > ?)',
    [1000]
  )
  .get();

// Users who wrote at least one post with > 1000 views

union / union all

AdvancedQueryBuilder.union(queries, all?) is a static method. Pass an array of query builder instances. all = true uses UNION ALL.

// AdvancedQueryBuilder.union(queries, all?)
// Static method — combines multiple queries with UNION or UNION ALL

import { AdvancedQueryBuilder } from 'slintorm';

const admins = User.advanced().where('role', '=', 'admin');
const mods   = User.advanced().where('role', '=', 'moderator');

// UNION — deduplicates rows
const staff = await AdvancedQueryBuilder.union([admins, mods]);

// UNION ALL — keeps duplicates
const allStaff = await AdvancedQueryBuilder.union([admins, mods], true);

Combined example

// Combining selectSubquery + exists + whereRaw
const result = await Post.advanced()
  .select('id', 'title')
  .selectSubquery(
    'SELECT COUNT(*) FROM comments WHERE comments.postId = posts.id',
    'commentCount'
  )
  .exists('SELECT 1 FROM likes WHERE likes.postId = posts.id')
  .whereRaw('posts.publishedAt IS NOT NULL')
  .orderBy('createdAt', 'DESC')
  .limit(20)
  .get();

PreviousAggregates NextRelation Traversal