Subqueries and Joins

BigAl supports subqueries for WHERE clauses, scalar comparisons, and joins. All subqueries are type-safe and composable.

Creating subqueries

Use the subquery() function:

import { subquery } from 'bigal';

const activeStores = subquery(storeRepository).select(['id']).where({ isActive: true });

SubqueryBuilder methods:

Method	Description
select(columns)	Columns and/or aggregates to select
where(query)	Filter rows
sort(value)	Order results
limit(n)	Limit rows
groupBy(columns)	Group for aggregation
having(condition)	Filter groups by aggregate values
distinctOn(columns)	PostgreSQL DISTINCT ON

WHERE IN / NOT IN

const activeStores = subquery(storeRepository).select(['id']).where({ isActive: true });

// WHERE IN
const products = await productRepository.find().where({
  store: { in: activeStores },
});
// SQL: WHERE "store_id" IN (SELECT "id" FROM "stores" WHERE "is_active"=$1)

// WHERE NOT IN
const products = await productRepository.find().where({
  store: { '!': { in: activeStores } },
});

WHERE EXISTS / NOT EXISTS

const hasProducts = subquery(productRepository).where({ name: { like: 'Widget%' } });

// EXISTS
const stores = await storeRepository.find().where({
  exists: hasProducts,
});

// NOT EXISTS
const stores = await storeRepository.find().where({
  '!': { exists: hasProducts },
});

If no columns are selected in the subquery, it defaults to SELECT 1.

Scalar subquery comparisons

Compare column values against single-value subquery results:

const avgPrice = subquery(productRepository).avg('price');

const expensiveProducts = await productRepository.find().where({
  price: { '>': avgPrice },
});
// SQL: WHERE "price">(SELECT AVG("price") FROM "products")

Supported operators: >, >=, <, <=, '!' (not equal), or direct equality.

// Equal to max price
.where({ price: subquery(productRepository).max('price') })

// Not equal to min price
.where({ price: { '!': subquery(productRepository).min('price') } })

Model joins

Join to related entities defined in your model:

// Inner join
const products = await productRepository
  .find()
  .join('store')
  .where({ store: { name: 'Acme' } });
// SQL: SELECT "products"."id","products"."name",… FROM "products"
//   INNER JOIN "stores" AS "store" ON "products"."store_id"="store"."id"
//   WHERE "store"."name"=$1

// Left join
const products = await productRepository
  .find()
  .leftJoin('store')
  .where({ store: { name: 'Acme' } });

// Custom alias
const products = await productRepository
  .find()
  .join('store', 'primaryStore')
  .where({ primaryStore: { name: 'Acme' } });

// Additional ON conditions (left join only)
const products = await productRepository.find().leftJoin('store', 'activeStore', { isActive: true });

When a query includes any join, BigAl automatically qualifies the base table's own columns (in SELECT, WHERE, ORDER BY, and DISTINCT ON) with the base table name. This keeps columns that share a name across the base and joined tables (most commonly id) from being ambiguous, so .join() is safe to use without falling back to raw SQL.

.join() vs .populate()

.join() and .populate() solve different problems, and you do not need .join() to populate a relation:

• .join() adds a SQL JOIN to the main query so you can filter or sort the base results by columns on the related table. It does not hydrate the related entity onto a nested property.
• .populate() loads the related records. It runs a separate query after the main query resolves (not a JOIN) and nests the results, so it works on its own.

Use .join() alongside .populate() only when you also need to constrain the base results by the related table. Without such a constraint, .populate() by itself is enough.

Subquery joins

Join to subquery results:

const productCounts = subquery(productRepository)
  .select(['store', (sb) => sb.count().as('productCount')])
  .groupBy(['store']);

// Inner join
const stores = await storeRepository.find().join(productCounts, 'stats', { on: { id: 'store' } });
// SQL: SELECT "stores"."id","stores"."name" FROM "stores"
//   INNER JOIN (
//     SELECT "store_id" AS "store", COUNT(*) AS "productCount"
//     FROM "products" GROUP BY "store_id"
//   ) AS "stats" ON "stores"."id"="stats"."store"

// Left join
const stores = await storeRepository.find().leftJoin(productCounts, 'stats', { on: { id: 'store' } });

Multiple ON conditions:

const categoryStats = subquery(productRepository)
  .select(['store', 'category', (sb) => sb.count().as('count')])
  .groupBy(['store', 'category']);

const stores = await storeRepository.find().join(categoryStats, 'stats', { on: { id: 'store', categoryId: 'category' } });

Sorting on joined columns

Use dot notation to sort by joined table columns:

// Model join
const products = await productRepository.find().join('store').sort('store.name asc');

// Subquery join
const stores = await storeRepository
  .find()
  .join(productCounts, 'stats', { on: { id: 'store' } })
  .sort('stats.productCount desc');

Aggregate functions

Available in subquery selects:

Function	Description
count()	Count all rows
count(column)	Count non-null values
count(column).distinct()	Count distinct values
sum(column)	Sum numeric values
avg(column)	Average numeric values
max(column)	Maximum value
min(column)	Minimum value

const stats = subquery(productRepository)
  .select(['store', (sb) => sb.count().as('totalProducts'), (sb) => sb.sum('price').as('totalValue'), (sb) => sb.avg('price').as('avgPrice'), (sb) => sb.count('name').distinct().as('uniqueNames')])
  .groupBy(['store']);

If .as() is not called, aggregates use their function name as the alias (e.g. count, sum).

GROUP BY and HAVING

const popularCategories = subquery(productRepository)
  .select(['category', (sb) => sb.count().as('productCount')])
  .groupBy(['category'])
  .having({ productCount: { '>': 10 } });
// SQL: ... GROUP BY "category_id" HAVING COUNT(*)>10

HAVING operators:

Syntax	SQL
{ alias: 5 }	HAVING AGG(*)=5
{ alias: { '>': 5 } }	HAVING AGG(*)>5
{ alias: { '>=': 5 } }	HAVING AGG(*)>=5
{ alias: { '<': 5 } }	HAVING AGG(*)<5
{ alias: { '<=': 5 } }	HAVING AGG(*)<=5
{ alias: { '!=': 5 } }	HAVING AGG(*)<>5

Multiple conditions:

.having({ productCount: { '>=': 5, '<=': 100 } })
// SQL: HAVING COUNT(*)>=5 AND COUNT(*)<=100

Type-safe subquery sorting

Annotate aggregate callbacks with TypedAggregateExpression for compile-time column validation:

import type { TypedAggregateExpression } from 'bigal';

const productCounts = subquery(productRepository)
  .select([
    'store',
    (sb): TypedAggregateExpression<'productCount'> => sb.count().as('productCount'),
  ])
  .groupBy(['store']);

const stores = await storeRepository.find()
  .join(productCounts, 'stats', { on: { id: 'store' } })
  .sort('stats.productCount desc'); // Type-safe!

// @ts-expect-error - 'invalidColumn' is not a selected column
.sort('stats.invalidColumn desc');

DISTINCT ON in subqueries

Use distinctOn() for "greatest-per-group" patterns:

const latestProducts = subquery(productRepository).select(['store', 'name', 'createdAt']).distinctOn(['store']).sort('store').sort('createdAt desc');

const stores = await storeRepository.find().join(latestProducts, 'latestProduct', { on: { id: 'store' } });

See Querying > DISTINCT ON for constraints and usage with top-level queries.