Advanced Queries

SlintORM's query builder exposes a set of advanced methods for complex query patterns — grouped conditions, named parameters, multi-column IN, optimizer hints, post-fetch transforms, and a dry-run mode for debugging.

Group conditions

Use andWhereGroup(fn) and orWhereGroup(fn) to nest WHERE clauses inside parentheses with the desired boolean conjunction.

andWhereGroup

// andWhereGroup(fn) — nest conditions with AND
const users = await User.query()
  .where('active', '=', true)
  .andWhereGroup(q => {
    q.where('role', '=', 'admin')
     .orWhere('role', '=', 'moderator');
  })
  .get();

// SQL: SELECT * FROM users
//      WHERE active = true
//        AND (role = ? OR role = ?)

orWhereGroup

// orWhereGroup(fn) — nest conditions with OR
const posts = await Post.query()
  .orWhereGroup(q => {
    q.where('status', '=', 'draft')
     .where('authorId', '=', 42);
  })
  .orWhereGroup(q => {
    q.where('status', '=', 'published')
     .where('featured', '=', true);
  })
  .get();

// SQL: SELECT * FROM posts
//      WHERE (status = ? AND authorId = ?)
//         OR (status = ? AND featured = ?)

Named arguments

namedWhere(sql, params) lets you write raw SQL fragments with named placeholders (:name, @name, $name) that are replaced with positional or numbered placeholders per driver.

// namedWhere(sql, params) — named placeholder replacement
const users = await User.query()
  .where('active', '=', true)
  .namedWhere('name LIKE :pattern AND age > :minAge', {
    pattern: 'A%',
    minAge: 18,
  })
  .get();

// SQL: SELECT * FROM users
//      WHERE active = true
//        AND name LIKE ? AND age > ?
// params: ['A%', 18]

// Supports :name, @name, and $name placeholder styles

Multi-column IN

whereColumnsIn(columns[], values[][]) generates a composite IN clause matching tuples of columns against tuples of values. The placeholder style adapts to each driver.

// whereColumnsIn(columns[], values[][]) — composite IN
const results = await Product.query()
  .whereColumnsIn(
    ['category', 'status'],
    [
      ['electronics', 'active'],
      ['clothing', 'active'],
      ['books', 'inactive'],
    ]
  )
  .get();

// SQL (Postgres): SELECT * FROM products
//      WHERE (category, status) IN (($1,$2),($3,$4),($5,$6))
// SQL (MySQL):    SELECT * FROM products
//      WHERE (category, status) IN ((?,?),(?,?),(?,?))
// SQL (SQLite):   SELECT * FROM products
//      WHERE (category, status) IN (?,?,?)
//      -- SQLite flattens to positional placeholders
// params: ['electronics', 'active', 'clothing', 'active', 'books', 'inactive']

Query hints

hint(str) attaches an optimizer hint directly afterSELECT. commentHint(str) attaches a SQL comment for monitoring tools. Support varies by driver.

// hint(hintString) — attach optimizer hints
// Postgres:  /*+ NO_INDEX */  (pg_hint_plan extension)
// MySQL:     SELECT /*+ NO_INDEX(users idx_email) */ ...
// SQLite:    ignored (no hint support)

const users = await User.query()
  .hint('/*+ NO_INDEX(users idx_email) */')
  .where('email', '=', 'joe@example.com')
  .get();

// The hint is inserted after SELECT:
// SELECT /*+ NO_INDEX(users idx_email) */ * FROM users ...

// commentHint(comment) — attach a SQL comment hint
// Useful for database monitoring tools (e.g., pgbadger, MySQL slow query log)

const users = await User.query()
  .commentHint('report: daily-active-users')
  .where('active', '=', true)
  .get();

// SQL: SELECT * FROM users /* report: daily-active-users */
//      WHERE active = true

AfterFind hook

afterFind(fn) registers a transform function that runs on the result set after the query executes. The function receives the rows and must return the transformed rows. Supports async transforms.

// afterFind(fn) — transform rows after fetch
const users = await User.query()
  .where('active', '=', true)
  .afterFind(rows =>
    rows.map(user => ({
      ...user,
      fullName: `${user.firstName} ${user.lastName}`,
    }))
  )
  .get();

// Each row in the returned array has the computed fullName field
// The original entities are not modified — afterFind returns new objects

// afterFind can be async
const enriched = await Order.query()
  .where('status', '=', 'pending')
  .afterFind(async orders => {
    // Fetch additional data for each order
    const enriched = await Promise.all(
      orders.map(async order => ({
        ...order,
        customer: await Customer.get({ id: order.customerId }),
      }))
    );
    return enriched;
  })
  .get();

Dry-run mode

dryRun() returns { sql, params } instead of executing the query. Works on all query types including insert, update, and delete. Essential for debugging generated SQL.

// dryRun() — returns { sql, params } without executing
const query = User.query()
  .where('role', '=', 'admin')
  .whereNotNull('verifiedAt')
  .orderBy('name', 'ASC')
  .limit(10)
  .dryRun();

console.log(query.sql);
// "SELECT * FROM users WHERE role = ? AND verifiedAt IS NOT NULL ORDER BY name ASC LIMIT ?"

console.log(query.params);
// ['admin', 10]

// Useful for debugging, logging, or manual query inspection

// dryRun() also works on insert/update/delete
const insertQuery = User.insert({
  email: 'test@example.com',
  name: 'Test',
}).dryRun();

console.log(insertQuery.sql);
// "INSERT INTO users (email, name, createdAt, updatedAt) VALUES (?, ?, ?, ?)"
console.log(insertQuery.params);
// ['test@example.com', 'Test', ...]

PreviousScopes & ExtendedQueryBuilder NextWindow Functions