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

# Deep Relations

> Load related entities with $populate, and filter, sort and count across relations.

Source: https://uql-orm.dev/querying/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` stays for the entity’s own columns:

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

PostgreSQL:

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

MySQL:

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

MariaDB:

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

SQLite:

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

SQL Server:

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

```ts
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:

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

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

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

MariaDB:

```sql
SET STATEMENT group_concat_max_len=18446744073709551615 FOR
SELECT `User`.`id`, `User`.`name`,
  COALESCE((SELECT JSON_ARRAYAGG(JSON_OBJECT('title', `posts`.`title`, 'createdAt', `posts`.`createdAt`)
              ORDER BY `posts`.`createdAt` DESC LIMIT 5)
            FROM `Post` `posts`
            WHERE LOWER(`posts`.`title`) LIKE ? ESCAPE '\\' AND `posts`.`authorId` = `User`.`id`), JSON_ARRAY()) `posts`
FROM `User`
LIMIT 20
```

SQLite:

```sql
SELECT `User`.`id`, `User`.`name`,
  (SELECT json_group_array(json_object('title', `posts`.`title`, 'createdAt', `posts`.`createdAt`)
            ORDER BY `posts`.`_uql_sort_createdAt` DESC)
     FROM (SELECT `posts`.`title`, `posts`.`createdAt`, `posts`.`createdAt` `_uql_sort_createdAt`
           FROM `Post` `posts`
           WHERE `posts`.`title` LIKE ? ESCAPE '\' AND `posts`.`authorId` = `User`.`id`
           ORDER BY `_uql_sort_createdAt` DESC LIMIT 5) `posts`) `posts`
FROM `User`
LIMIT 20
```

SQL Server:

```sql
SELECT "User"."id", "User"."name",
  JSON_QUERY(COALESCE((SELECT "posts"."title",
                              CONVERT(VARCHAR(33), CAST("posts"."createdAt" AS DATETIMEOFFSET), 127) "createdAt"
                       FROM "Post" "posts"
                       WHERE LOWER("posts"."title") LIKE @p1 ESCAPE N'\' AND "posts"."authorId" = "User"."id"
                       ORDER BY "posts"."createdAt" DESC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
                       FOR JSON PATH, INCLUDE_NULL_VALUES), '[]')) "posts"
FROM "User"
ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
```

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`, `$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:

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

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

MySQL:

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

MariaDB:

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

SQLite:

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

SQL Server:

```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 only sorted by, so its join adds no columns. It is the join `$populate` would make, [filters](https://uql-orm.dev/querying/filters.md) 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](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)).
- 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:

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

PostgreSQL:

```sql
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 '\'
  )
```

MySQL:

```sql
SELECT `id`, `name`
FROM `User`
WHERE LOWER(`name`) LIKE ? ESCAPE '\\'
  AND EXISTS (
    SELECT 1 FROM `Post` `posts`
    WHERE `posts`.`authorId` = `User`.`id` AND LOWER(`posts`.`title`) LIKE ? ESCAPE '\\'
  )
```

MariaDB:

```sql
SELECT `id`, `name`
FROM `User`
WHERE LOWER(`name`) LIKE ? ESCAPE '\\'
  AND EXISTS (
    SELECT 1 FROM `Post` `posts`
    WHERE `posts`.`authorId` = `User`.`id` AND LOWER(`posts`.`title`) LIKE ? ESCAPE '\\'
  )
```

SQLite:

```sql
SELECT `id`, `name`
FROM `User`
WHERE `name` LIKE ? ESCAPE '\'
  AND EXISTS (
    SELECT 1 FROM `Post` `posts`
    WHERE `posts`.`authorId` = `User`.`id` AND `posts`.`title` LIKE ? ESCAPE '\'
  )
```

SQL Server:

```sql
SELECT "id", "name"
FROM "User"
WHERE LOWER("name") LIKE @p1 ESCAPE N'\'
  AND EXISTS (
    SELECT 1 FROM "Post" "posts"
    WHERE "posts"."authorId" = "User"."id" AND LOWER("posts"."title") LIKE @p2 ESCAPE N'\'
  )
```

For a many-to-many, the subquery goes through the junction table. The [filters](https://uql-orm.dev/querying/filters.md) 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](https://uql-orm.dev/querying/comparison-operators.md):

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

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

MySQL:

```sql
SELECT `id`, `name`
FROM `MeasureUnitCategory`
WHERE (SELECT COUNT(*) FROM `MeasureUnit` `measureUnits`
       WHERE `measureUnits`.`categoryId` = `MeasureUnitCategory`.`id`
         AND `measureUnits`.`deletedAt` IS NULL) >= ?
```

MariaDB:

```sql
SELECT `id`, `name`
FROM `MeasureUnitCategory`
WHERE (SELECT COUNT(*) FROM `MeasureUnit` `measureUnits`
       WHERE `measureUnits`.`categoryId` = `MeasureUnitCategory`.`id`
         AND `measureUnits`.`deletedAt` IS NULL) >= ?
```

SQLite:

```sql
SELECT `id`, `name`
FROM `MeasureUnitCategory`
WHERE (SELECT COUNT(*) FROM `MeasureUnit` `measureUnits`
       WHERE `measureUnits`.`categoryId` = `MeasureUnitCategory`.`id`
         AND `measureUnits`.`deletedAt` IS NULL) >= ?
```

SQL Server:

```sql
SELECT "id", "name"
FROM "MeasureUnitCategory"
WHERE (SELECT COUNT(*) FROM "MeasureUnit" "measureUnits"
       WHERE "measureUnits"."categoryId" = "MeasureUnitCategory"."id"
         AND "measureUnits"."deletedAt" IS NULL) >= @p1
```

The count applies the related entity’s filters, as `EXISTS` does. To return or sort by a relation’s size instead, see [counting relations](https://uql-orm.dev/querying/counting.md#counting-relations).

## Next steps

- [Relation Mapping](https://uql-orm.dev/entities/relations.md): how the relations you query here are declared.
- [Sub-Queries](https://uql-orm.dev/querying/sub-queries.md): correlated sub-queries when the built-in ones are not enough.
- [Streaming](https://uql-orm.dev/querying/streaming.md): the same relations, row by row.
