Query Builder — Aggregations & Subqueries

GROUP BY, HAVING, subqueries, and DISTINCT are where find() runs out of expressiveness. This page covers the full set, with examples that stay compatible across MySQL, PostgreSQL, and SQLite.

GROUP BY and Aggregation

Suppose you want to count how many posts each category has. This requires GROUP BY.

import sql from "sql-template-tag";

const stats = await em
  .createQueryBuilder(Post, "p")
  .select(["category"])
  .addSelect(sql`COUNT(*)`, "postCount")
  .groupBy(["category"])
  .having(sql`COUNT(*) >= ${5}`)
  .getRawMany();
// [{ category: "tech", postCount: 42 }, { category: "life", postCount: 17 }, ...]

groupBy() takes an array of column names (type-safe). having() filters groups after aggregation — here, only categories with 5 or more posts.

Note — for a typed aggregate surface (u.id.count(), .sum(), .avg(), .min(), .max()) that plugs into both SELECT and HAVING without sql templates, see the QueryDSL Aggregates section.

Aggregate Functions in SELECT

Use addSelect() with sql template literals to add aggregate columns. The column alias becomes the key in the result object.

import sql from "sql-template-tag";

const stats = await em
  .createQueryBuilder(Order, "o")
  .addSelect(sql`AVG("o"."price")`, "avgPrice")
  .addSelect(sql`SUM("o"."price")`, "totalRevenue")
  .addSelect(sql`MIN("o"."price")`, "cheapest")
  .addSelect(sql`MAX("o"."price")`, "mostExpensive")
  .addSelect(sql`COUNT(*)`, "orderCount")
  .getRawMany();
// [{ avgPrice: 42.5, totalRevenue: 850, cheapest: 10, mostExpensive: 99, orderCount: 20 }]

You can combine aggregate columns with grouped entity columns:

const salesByCategory = await em
  .createQueryBuilder(Product, "p")
  .select(["category"])
  .addSelect(sql`AVG("p"."price")`, "avgPrice")
  .addSelect(sql`COUNT(*)`, "productCount")
  .groupBy(["category"])
  .having(sql`AVG("p"."price") > ${50}`)
  .orderBy({ category: "ASC" })
  .getRawMany();
// [{ category: "electronics", avgPrice: 299.99, productCount: 15 }, ...]

With entity-aware joins, you can aggregate across related tables:

const p = qAlias(Post, "p");
const u = qAlias(User, "u");

const authorStats = await em
  .createQueryBuilder(Post, "p")
  .leftJoin(User, "u", (join) => join.on(p.col("authorId"), "=", u.col("id")))
  .selectRaw([u.col("name")])
  .addSelect(sql`COUNT(*)`, "postCount")
  .addSelect(sql`AVG("p"."likeCount")`, "avgLikes")
  .groupBy([u.col("name")])
  .having(sql`COUNT(*) >= ${3}`)
  .addOrderBy("postCount", "DESC")
  .getRawMany();
// [{ name: "Alice", postCount: 12, avgLikes: 45.3 }, ...]

Subqueries

The query builder supports subqueries in WHERE, SELECT, and FROM clauses. While find() can't express subqueries at all, the query builder makes them straightforward.

WHERE IN Subquery

Use Conditions.inSubquery() or pass a built Sql object from another query builder:

import sql from "sql-template-tag";
import { Conditions } from "@stingerloom/orm";

// Find posts by authors from Korea
const subquery = em
  .createQueryBuilder(User, "u")
  .select(["id"])
  .where("country", "KR")
  .toSql();

const posts = await em
  .createQueryBuilder(Post, "p")
  .where(Conditions.inSubquery(`"p"."authorId"`, sql`(${subquery})`))
  .getMany();

WHERE EXISTS / NOT EXISTS

Use Conditions.exists() or Conditions.notExists() for correlated subqueries:

// Find authors who have at least one published post
const posts = await em
  .createQueryBuilder(Post, "p")
  .where("authorId", sql`"a"."id"`)
  .where("status", "published")
  .toSql();

const authors = await em
  .createQueryBuilder(User, "a")
  .where(Conditions.exists(sql`(SELECT 1 FROM "post" "p" WHERE "p"."author_id" = "a"."id" AND "p"."status" = ${"published"})`))
  .getMany();

Scalar Subquery in SELECT

Use addSelect() to add a scalar subquery as a computed column:

const authors = await em
  .createQueryBuilder(User, "u")
  .select(["id", "name"])
  .addSelect(
    sql`(SELECT COUNT(*) FROM "post" "p" WHERE "p"."author_id" = "u"."id")`,
    "postCount",
  )
  .getRawMany();
// [{ id: 1, name: "Alice", postCount: 5 }, { id: 2, name: "Bob", postCount: 0 }]

FROM Subquery (Derived Table)

Use asSubquery() to turn a SelectQueryBuilder into a derived table, then query it with a RawQueryBuilder:

// Step 1: Build the inner query
const inner = em
  .createQueryBuilder(Post, "p")
  .select(["authorId"])
  .addSelect(sql`COUNT(*)`, "cnt")
  .groupBy(["authorId"]);

// Step 2: Use it as a derived table
const qb = em.createQueryBuilder();
const results = await em.query(
  qb
    .select(['"sub"."authorId"', '"sub"."cnt"'])
    .from(inner.asSubquery("sub").sql)
    .where([sql`"sub"."cnt" >= ${3}`])
    .build()
);
// Authors with 3+ posts

CTE (Common Table Expressions)

For complex multi-step queries, use CTE via the RawQueryBuilder:

const qb = em.createQueryBuilder();

const results = await em.query(
  qb
    .with("active_authors", (sub) =>
      sub
        .select(['DISTINCT "authorId"'])
        .from('"post"')
        .where([sql`"status" = ${"published"}`])
    )
    .select(['"u"."id"', '"u"."name"'])
    .from('"user" "u"')
    .where([sql`"u"."id" IN (SELECT "authorId" FROM "active_authors")`])
    .build()
);

For recursive CTEs (hierarchical data like comment threads or org charts), see the Raw SQL & CTE guide.

Higher-level helpers on SelectQueryBuilder

When the subquery is itself a SelectQueryBuilder, prefer the typed helpers — they keep parameter bindings threaded through and give you full autocomplete on the inner query:

• whereInSubquery(column, subQb) / whereNotInSubquery(...)
• whereExistsSubquery(subQb) / whereNotExistsSubquery(...)
• addSelectSubquery(subQb, alias)

See Patterns & Productivity for the full set.

DISTINCT

When you want unique rows only, enable DISTINCT.

const uniqueCities = await em
  .createQueryBuilder(User, "u")
  .select(["city"])
  .setDistinct()
  .getPartialMany();
// SELECT DISTINCT "u"."city" FROM "user" AS "u"

This removes duplicate rows from the result set. Useful when selecting a subset of columns where many rows may share the same values.

For DISTINCT ON (PostgreSQL-specific), see the Raw SQL & CTE guide.

Next Steps

• QueryDSL Expressions — typed aggregates, window functions, CASE, date components
• Patterns & Productivity — whereHas, withCount, scopes
• Raw SQL & CTE — UNION, recursive CTE, window functions
• Query Builder Overview — basics and overall map