Deep Relations

$populate loads related entities with the rows you query, in the same statement. Inside it, fields and operators are completed and type-checked against the related entity. A relation you do not populate is not loaded: nothing is lazy. You can also filter and sort parents by their relations without loading them.

Loading a relation

Name the relation in $populate and pick its fields there. $select and $exclude stay for the entity’s own columns:

You write

import { pool } from './uql.config.js';
import { User } from './shared/models/index.js';

const users = await pool.findMany(User, {
  $select: { id: true, name: true },
  $populate: {
    profile: { $select: { picture: true } },
  },
});

PostgreSQL

SELECT "User"."id", "User"."name",
       "profile"."id" "profile.id",
       "profile"."picture" "profile.picture"
FROM "User"
LEFT JOIN "Profile" "profile" ON "profile"."userId" = "User"."id"

The relation’s primary key always comes back. A to-one relation is joined with a LEFT JOIN, so parents without a match are kept. $required: true makes it an INNER JOIN, which keeps only the parents that have one. A $where inside the relation goes into the join condition:

const withBio = await pool.findMany(User, {
  $populate: {
    profile: {
      $select: { bio: true },
      $where: { bio: { $ne: null } },
      $required: true,
    },
  },
});

Filtering, sorting and paging a collection

A populated one-to-many or many-to-many takes its own $where, $sort, $limit and $skip, applied per parent. This loads each author’s 5 newest posts about TypeScript:

You write

const authors = await pool.findMany(User, {
  $select: { id: true, name: true },
  $populate: {
    posts: {
      $select: { title: true, createdAt: true },
      $where: { title: { $iincludes: 'typescript' } },
      $sort: { createdAt: 'desc' },
      $limit: 5,
    },
  },
  $limit: 20,
});

PostgreSQL

SELECT "User"."id", "User"."name",
  (SELECT COALESCE(JSON_AGG("_uql_row" ORDER BY "posts"."_uql_sort_createdAt" DESC), '[]'::json)
     FROM (SELECT "posts"."title", "posts"."createdAt", "posts"."createdAt" "_uql_sort_createdAt"
           FROM "Post" "posts"
           WHERE "posts"."title" ILIKE $1 ESCAPE '\' AND "posts"."authorId" = "User"."id"
           ORDER BY "_uql_sort_createdAt" DESC LIMIT 5) "posts"
     CROSS JOIN LATERAL (SELECT "posts"."title", "posts"."createdAt") "_uql_row") "posts"
FROM "User"
LIMIT 20

MySQL

SELECT /*+ SET_VAR(group_concat_max_len=18446744073709551615) */ `User`.`id`, `User`.`name`,
  (SELECT COALESCE(CONCAT('[', GROUP_CONCAT(JSON_OBJECT('title', `posts`.`title`, 'createdAt', `posts`.`createdAt`)
            ORDER BY `posts`.`_uql_sort_createdAt` DESC SEPARATOR ','), ']'), '[]')
     FROM (SELECT `posts`.`title`, `posts`.`createdAt`, `posts`.`createdAt` `_uql_sort_createdAt`
           FROM `Post` `posts`
           WHERE LOWER(`posts`.`title`) LIKE ? ESCAPE '\\' AND `posts`.`authorId` = `User`.`id`
           ORDER BY `_uql_sort_createdAt` DESC LIMIT 5) `posts`) `posts`
FROM `User`
LIMIT 20

It is still one statement. A parent with no matches gets [], and the row types the relation as a list, so author.posts.map(...) needs no guard. $limit: 1 with a $sort gives each parent its latest row. Limit the parents too, since the statement reads up to parents times $limit related rows.

A to-one $populate rejects $sort, $limit, $skip and $distinct, since it joins one row per parent. $select, $exclude, $where and $required work on both kinds.

Sorting by related fields

$sort can name a field on a to-one relation, populated or not. UQL adds the join the sort needs:

You write

const items = await pool.findMany(Item, {
  $select: { id: true, name: true },
  $populate: { tax: { $select: { name: true } } },
  $sort: { tax: { name: 1 }, measureUnit: { name: 1 }, createdAt: 'desc' },
});

PostgreSQL

SELECT "Item"."id", "Item"."name", "tax"."id" "tax.id", "tax"."name" "tax.name"
FROM "Item"
LEFT JOIN "Tax" "tax" ON "tax"."id" = "Item"."taxId" AND "tax"."deletedAt" IS NULL
LEFT JOIN "MeasureUnit" "measureUnit" ON "measureUnit"."id" = "Item"."measureUnitId" AND "measureUnit"."deletedAt" IS NULL
ORDER BY "tax"."name", "measureUnit"."name", "Item"."createdAt" DESC

measureUnit is only sorted by, so its join adds no columns. It is the join $populate would make, filters and soft-delete included, so the sort never sees a row the query could not read. A nested path joins each level: $sort: { measureUnit: { category: { name: 1 } } }.

A sort by a relation is rejected:

• On a to-many relation’s fields: a parent has many such rows, so there is no single value to order by. Sort them inside $populate, or rank parents by the relation’s size, { posts: { $count: -1 } } (counting), or by its row nearest a vector (semantic search).
• In updateMany, deleteMany and $group aggregates, which do not join.
• With $distinct, unless the relation is populated too, because SELECT DISTINCT orders only by selected columns.
• On MongoDB, unless every level of the path is populated, since $lookup is what brings the fields in.

Filtering by a relation

A condition on a one-to-many or many-to-many relation keeps the parents that have a matching row. It compiles to an EXISTS subquery, so parent rows are never duplicated:

You write

const users = await pool.findMany(User, {
  $select: { id: true, name: true },
  $where: {
    name: { $istartsWith: 'a' },
    posts: { title: { $iincludes: 'typescript' } },
  },
});

PostgreSQL

SELECT "id", "name" FROM "User"
WHERE "name" ILIKE $1 ESCAPE '\'
  AND EXISTS (
    SELECT 1 FROM "Post" "posts"
    WHERE "posts"."authorId" = "User"."id" AND "posts"."title" ILIKE $2 ESCAPE '\'
  )

For a many-to-many, the subquery goes through the junction table. The filters of the related entity and of the junction apply inside it, so a parent never matches through a soft-deleted child or one outside a security filter. On MongoDB the same conditions compile to correlated $lookup stages.

Filtering by count ($size)

$size filters parents by how many related rows they have. It takes a number for an exact match, or a comparison operator:

You write

const categories = await pool.findMany(MeasureUnitCategory, {
  $select: { id: true, name: true },
  $where: { measureUnits: { $size: { $gte: 2 } } },
});
const items = await pool.findMany(Item, {
  $where: { tags: { $size: { $between: [2, 10] } } },
});

PostgreSQL

SELECT "id", "name" FROM "MeasureUnitCategory"
WHERE (SELECT COUNT(*) FROM "MeasureUnit" "measureUnits"
       WHERE "measureUnits"."categoryId" = "MeasureUnitCategory"."id"
         AND "measureUnits"."deletedAt" IS NULL) >= $1

The count applies the related entity’s filters, as EXISTS does. To return or sort by a relation’s size instead, see counting relations.

Next steps

• Relation Mapping: how the relations you query here are declared.
• Sub-Queries: correlated sub-queries when the built-in ones are not enough.
• Streaming: the same relations, row by row.