Deep Relations

Scalars and relations are addressed separately: $select and $exclude take local scalar columns (strings, numbers, dates, JSONB), and $populate takes related entity graphs.

Querying relations

UQL’s query syntax is context-aware. When you query a relation, the available fields and operators are automatically suggested and validated based on that related entity.

Basic Population

You can load a relation and select its specific fields using $populate.

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 } }, // Load specific fields from a 1-1 relation
  },
  $where: {
    email: { $iincludes: '@example.com' },
  },
});

PostgreSQL

-- Main query with LEFT JOIN for OneToOne relation
SELECT "User"."id", "User"."name",
       "profile"."id" "profile.id", -- the relation's primary key is always selected
       "profile"."picture" "profile.picture" -- Prefixed alias for unflattening
FROM "User"
LEFT JOIN "Profile" "profile" ON "profile"."userId" = "User"."id" AND "profile"."deletedAt" IS NULL
WHERE "User"."email" ILIKE $1 AND "User"."deletedAt" IS NULL
-- values: ['%@example.com%']

Advanced: Deep Selection & Mandatory Relations

Use $required: true inside a $populate block to enforce an INNER JOIN (by default UQL uses LEFT JOIN).

You write

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

const latestUsersWithProfiles = await pool.findOne(User, {
  $select: { id: true, name: true },
  $populate: {
    profile: {
      $select: { picture: true, bio: true },
      $where: { bio: { $ne: null } },
      $required: true, // Enforce INNER JOIN
    },
  },
  $sort: { createdAt: 'desc' },
});

PostgreSQL

-- INNER JOIN enforced by $required: true
SELECT "User"."id", "User"."name",
       "profile"."id" "profile.id",
       "profile"."picture" "profile.picture", "profile"."bio" "profile.bio"
FROM "User"
INNER JOIN "Profile" "profile" ON "profile"."userId" = "User"."id"
  AND "profile"."bio" IS NOT NULL AND "profile"."deletedAt" IS NULL
WHERE "User"."deletedAt" IS NULL
ORDER BY "User"."createdAt" DESC
LIMIT 1

Filtering on Related Collections

You can filter and sort when populating collections (One-to-Many or Many-to-Many).

You write

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

const authorsWithPopularPosts = 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,
    },
  },
  $where: {
    name: { $istartsWith: 'a' },
  },
});

PostgreSQL

-- Main query (parent rows)
SELECT "User"."id", "User"."name" FROM "User"
WHERE "User"."name" ILIKE $1 AND "User"."deletedAt" IS NULL
-- values: ['a%']

PostgreSQL, separate query

-- OneToMany relation loaded via a second query. It joins nothing, so its columns are bare, and the
-- parent ids arrive as one array parameter rather than an expanded IN list.
SELECT "title", "createdAt", "authorId"
FROM "Post"
WHERE "title" ILIKE $1
  AND "authorId" = ANY($2)
  AND "deletedAt" IS NULL
ORDER BY "createdAt" DESC
LIMIT 5
-- values: ['%typescript%', [...parentIds]]

$limit on a to-many relation is global, not per parent

A to-many relation is loaded with one second query covering every parent row, so $limit: 5 caps the whole result set at 5 posts, not 5 posts per author. This is what keeps population free of N+1 queries, but it means “the 5 newest posts of each author” is not expressible through $populate - run a per-parent query, or a window-function raw() query, when you need that.

$sort orders that same single result set, which does leave each parent’s rows in that order: one order over all the rows is still that order within any one parent’s. $limit and $skip are the pair that cannot be read per parent.

Those four keys - $sort, $limit, $skip and $distinct - describe a collection, so they belong to a to-many’s own query. A to-one $populate rejects them rather than ignoring them: it is joined, so it brings one row per parent, leaving nothing to order, page through or de-duplicate. $select, $exclude, $where and $required apply to either cardinality.

Sorting by Related Fields

$sort can name a field on a to-one relation whether or not you populate it. UQL adds the join the ordering needs; $populate decides only whether that relation’s columns come back with the rows:

You write

import { Item } from './shared/models/index.js';

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
WHERE "Item"."deletedAt" IS NULL
ORDER BY "tax"."name", "measureUnit"."name", "Item"."createdAt" DESC

measureUnit is sorted by but not populated, so its join adds no columns and the rows come back the shape they would without it. It is the same join $populate would have made, filters and soft-deletes included, so an ordering can never read a row the query itself cannot. Nested paths join each level: $sort: { tax: { category: { name: 1 } } }.

Sorting by a relation is rejected where it cannot mean anything, or where nothing can join it:

• a to-many, at compile time and at runtime: a parent has many of those rows, so there is nothing single to order it by. Order them inside $populate, which sorts the query they are loaded with.
• updateMany, deleteMany, $group aggregates: none of those statements join.
• $distinct, unless the relation is populated too: SELECT DISTINCT orders only by columns it selected.
• MongoDB, unless the relation is populated - every level of the path, since a $lookup is what puts its fields on the document.

 

Relation Filtering (EXISTS Subqueries)

Filter parent entities based on conditions on their ManyToMany or OneToMany relations. UQL compiles the condition to an EXISTS subquery, so it never joins in and duplicates parent rows.

The related entity’s own filters apply inside the subquery (the junction’s too, for ManyToMany), so a parent never matches through a row the query could not read - a trashed child, or one outside a security: true filter’s scope. On MongoDB the same conditions compile to correlated $lookup stages, so the query is unchanged across drivers.

ManyToMany

You write

import { Post } from './shared/models/index.js';

// Find all posts that have a tag named 'typescript'
const posts = await pool.findMany(Post, {
  $where: { tags: { name: 'typescript' } },
});

PostgreSQL

SELECT * FROM "Post"
WHERE EXISTS (
  SELECT 1 FROM "PostTag"
  WHERE "PostTag"."postId" = "Post"."id"
    AND "PostTag"."tagId" IN (
      SELECT "Tag"."id" FROM "Tag"
      WHERE "Tag"."name" = $1 AND "Tag"."deletedAt" IS NULL
    )
) AND "deletedAt" IS NULL

OneToMany

You write

// Find users who have authored posts with 'typescript' in the title
const users = await pool.findMany(User, {
  $where: { posts: { title: { $iincludes: 'typescript' } } },
});

PostgreSQL

SELECT * FROM "User"
WHERE EXISTS (
  SELECT 1 FROM "Post"
  WHERE "Post"."authorId" = "User"."id"
    AND "Post"."title" ILIKE $1 AND "Post"."deletedAt" IS NULL
) AND "deletedAt" IS NULL
-- values: ['%typescript%']

Relation filters sit alongside field comparisons and logical operators in the same $where:

const posts = await pool.findMany(Post, {
  $where: {
    title: { $istartsWith: 'guide' },
    tags: { name: 'important' },
  },
});

Relation Count Filtering ($size Subqueries)

To return or rank by a relation’s size rather than filter on it, see counting relations.

Filter parent entities by the number of related records using $size on a relation key, type-checked against your entity’s relations and compiled to a COUNT(*) subquery. Accepts a number for exact match or any comparison operator ($eq, $ne, $gt, $gte, $lt, $lte, $between).

The count is scoped by the related entity’s filters, exactly like the EXISTS form above, so it never counts rows the same query could not read.

OneToMany

You write

// Find categories with at least 2 measure units
const categories = await pool.findMany(MeasureUnitCategory, {
  $where: { measureUnits: { $size: { $gte: 2 } } },
});

PostgreSQL

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

ManyToMany

You write

// Find items with more than 5 tags
const items = await pool.findMany(Item, {
  $where: { tags: { $size: { $gt: 5 } } },
});

PostgreSQL

-- Tag has no filters of its own, so the junction count stands alone; when it does have
-- them, the counted junction rows narrow to the target ids that satisfy them.
SELECT * FROM "Item"
WHERE (SELECT COUNT(*) FROM "ItemTag"
       WHERE "ItemTag"."itemId" = "Item"."id") > $1
  AND "deletedAt" IS NULL

Multiple Comparison Operators

You write

// Find items with between 2 and 10 tags
const items = await pool.findMany(Item, {
  $where: { tags: { $size: { $between: [2, 10] } } },
});

An exact number works here too: $size: 3 is the same as $size: { $eq: 3 }.

---

Next Steps

• Sub-Queries: Correlated sub-queries when the built-in ones are not enough.
• Relation Mapping: How the relations you query here are declared.
• Soft Delete: The filter that joins add to populated relations.
• Streaming: Relation loading rules change when streaming.