Deep Relations

Scalars and relations are separate

• $select / $exclude: local scalar columns (strings, numbers, dates, JSONB).
• $populate: 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' }
  }
});

Generated SQL (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
-- 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' },
});

Generated SQL (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
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' }
  }
});

Generated SQL (PostgreSQL)

-- Main query (parent rows)
SELECT "User"."id", "User"."name" FROM "User"
WHERE "User"."name" ILIKE $1

Generated SQL (PostgreSQL, separate query)

-- OneToMany relation loaded via a second query
SELECT "Post"."title", "Post"."createdAt", "Post"."authorId"
FROM "Post"
WHERE "Post"."authorId" IN ($1, $2, ...)
  AND "Post"."title" ILIKE $3
ORDER BY "Post"."createdAt" DESC
LIMIT 5
-- values: [...parentIds, '%typescript%']

$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. Same for $sort: it orders that single result set. 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.

Sorting by Related Fields

UQL allows sorting by fields of related entities directly in the $sort object.

You write

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

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

Generated SQL (PostgreSQL)

SELECT "Item"."id", "Item"."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

 

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' } },
});

Generated SQL (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 "Post"."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' } } },
});

Generated SQL (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 "User"."deletedAt" IS NULL
-- values: ['%typescript%']

Combining with Other Filters

Relation filters compose naturally with regular field comparisons and logical operators:

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

Relation Count Filtering ($size Subqueries)

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 } } },
});

Generated SQL (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 } } },
});

Generated SQL (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

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] } } },
});

Note

$size with an exact number ($size: 3) also works for relation count (equivalent to $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.