> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# Deep Relations

> Populate, filter, and sort across related entities with $populate and relation operators.

Source: https://uql-orm.dev/querying/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

Inside a relation, fields and operators are completed and checked against the related entity.

### Basic Population

`$populate` loads a relation and selects its fields:

```ts title="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:

```sql
-- 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"
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`).

```ts title="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:

```sql
-- 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
ORDER BY "User"."createdAt" DESC
LIMIT 1
```

### Filtering on Related Collections

A populated collection (one-to-many or many-to-many) takes its own filter, sort and page:

```ts title="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' },
  },
  // Bound the page too: every parent row this returns reads up to 5 posts of its own.
  $limit: 20,
});
```

PostgreSQL:

```sql
-- One statement: each author's posts are a correlated subquery aggregated as JSON.
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 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"
WHERE "User"."name" ILIKE $2
LIMIT 20
-- values: ['%typescript%', 'a%']
```

MySQL:

```sql
-- MySQL orders the posts in GROUP_CONCAT; the hint lifts its 1 KB cap for this statement.
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 ? AND `posts`.`authorId` = `User`.`id`
           ORDER BY `_uql_sort_createdAt` DESC LIMIT 5) `posts`) `posts`
FROM `User`
WHERE LOWER(`User`.`name`) LIKE ?
LIMIT 20
-- values: ['%typescript%', 'a%']
```

`$limit`, `$skip` and `$sort` are **per parent**: `$limit: 5` gives every author their own 5 newest posts, still in one statement. A parent with no matches gets `[]`. So `$limit: 1` reads each parent’s latest row, which a to-one relation has no order to pick: `$populate: { posts: { $sort: { createdAt: 'desc' }, $limit: 1 } }`.

**Bound the parent page too**: the statement reads up to `parents x limit` related rows.

`$sort`, `$limit`, `$skip` and `$distinct` describe a collection, so a to-one `$populate` rejects them: it is joined, one row per parent, with nothing to order, page 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:

```ts title="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:

```sql
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 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](https://uql-orm.dev/querying/filters.md) 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’s fields**, 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. A to-many ranks its parent by its size, `{ posts: { $count: -1 } }` ([counting](https://uql-orm.dev/querying/counting.md#ordering-by-a-relations-size)), or by its row nearest a vector ([semantic search](https://uql-orm.dev/querying/semantic-search.md#ranking-by-a-related-row)).
- **`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 every level of the path is populated, 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](https://uql-orm.dev/querying/filters.md) apply inside the subquery (the junction’s too, for ManyToMany), so a parent never matches through a row the query could not read, such as a trashed child or one outside a `security: true` filter’s scope. On MongoDB the same conditions compile to correlated `$lookup` stages.

#### ManyToMany

```ts title="You write"
import { Item } from './shared/models/index.js';

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

PostgreSQL:

```sql
SELECT * FROM "Item"
WHERE EXISTS (
  SELECT 1 FROM "ItemTag"
  WHERE "ItemTag"."itemId" = "Item"."id"
    AND "ItemTag"."tagId" IN (SELECT "tags"."id" FROM "Tag" "tags" WHERE "tags"."name" = $1)
)
```

#### OneToMany

```ts title="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:

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

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

```ts
const items = await pool.findMany(Item, {
  $where: {
    name: { $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](https://uql-orm.dev/querying/counting.md#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](https://uql-orm.dev/querying/comparison-operators.md) (`$eq`, `$ne`, `$gt`, `$gte`, `$lt`, `$lte`, `$between`).

The count is scoped by the related entity’s [filters](https://uql-orm.dev/querying/filters.md), exactly like the `EXISTS` form above, so it never counts rows the same query could not read.

#### OneToMany

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

PostgreSQL:

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

#### ManyToMany

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

PostgreSQL:

```sql
-- 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

```ts title="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](https://uql-orm.dev/querying/sub-queries.md): Correlated sub-queries when the built-in ones are not enough.
- [Relation Mapping](https://uql-orm.dev/entities/relations.md): How the relations you query here are declared.
- [Soft Delete](https://uql-orm.dev/entities/soft-delete.md): The filter that joins add to populated relations.
- [Streaming](https://uql-orm.dev/querying/streaming.md): The same relations, row by row.