Eager loading

include loads associated rows alongside the parents. This guide is about the
mechanism: which statements actually run, in what order, and which include
options are honoured. For how to declare the associations themselves, see
Associations.

The examples use one shop schema throughout. Every SQL string on this page was
captured from the ORM against that schema. As in Querying, a
model built with prorm.define() sends its statements with the values already
escaped in, so that is the form shown here and in the query log.

import { Prorm, DataTypes } from 'ts-prorm-orm';

export const prorm = new Prorm({ dialect: 'postgres', host: 'localhost', database: 'shop' });

const pk = { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true };

export const User = prorm.define('User', {
  id: pk,
  name: { type: DataTypes.STRING(100) },
  email: { type: DataTypes.STRING(255) },
  status: { type: DataTypes.STRING(20) },
}, { tableName: 'users', timestamps: false });

export const Order = prorm.define('Order', {
  id: pk,
  userId: { type: DataTypes.INTEGER() },
  status: { type: DataTypes.STRING(20) },
  total: { type: DataTypes.DECIMAL(10, 2) },
}, { tableName: 'orders', timestamps: false });

export const OrderItem = prorm.define('OrderItem', {
  id: pk,
  orderId: { type: DataTypes.INTEGER() },
  productId: { type: DataTypes.INTEGER() },
  quantity: { type: DataTypes.INTEGER() },
}, { tableName: 'order_items', timestamps: false });

export const Product = prorm.define('Product', {
  id: pk,
  sku: { type: DataTypes.STRING(32) },
  name: { type: DataTypes.STRING(200) },
  price: { type: DataTypes.DECIMAL(10, 2) },
}, { tableName: 'products', timestamps: false });

export const Tag = prorm.define('Tag', {
  id: pk,
  name: { type: DataTypes.STRING(50) },
}, { tableName: 'tags', timestamps: false });

export const ProductTag = prorm.define('ProductTag', {
  productId: { type: DataTypes.INTEGER() },
  tagId: { type: DataTypes.INTEGER() },
}, { tableName: 'product_tags', timestamps: false });

User.hasMany(Order, { foreignKey: 'userId', as: 'orders' });
Order.belongsTo(User, { foreignKey: 'userId', as: 'customer' });
Order.hasMany(OrderItem, { foreignKey: 'orderId', as: 'items' });
OrderItem.belongsTo(Product, { foreignKey: 'productId', as: 'product' });
Product.belongsToMany(Tag, { through: { model: ProductTag }, foreignKey: 'productId', as: 'tags' });

The data behind the captured SQL: three users (Ada, Grace and Linus), Ada with
two orders and Grace with one, four order items, two products and two tags.

Separate queries, not JOINs

Prorm resolves an include with one additional query per association level,
matching children to parents in memory. It never widens the parent SELECT
into a JOIN.

const customers = await User.findAll({ include: [{ model: Order, as: 'orders' }] });
customers.map((u) => [u.name, u.orders.length]); // [['Ada', 2], ['Grace', 1], ['Linus', 0]]

Two statements serve any number of users, so there is no N+1 problem. And
because the parent query is never joined, LIMIT 20 on the parent means twenty
users, not twenty joined rows. The trade-off is that you cannot sort parents by
a child column in one statement. Do that with $alias.column$ filtering plus an
ordinary parent order, or drop to raw SQL.

Nesting adds one query per level, not per row:

const customers = await User.findAll({
  include: [{
    model: Order,
    as: 'orders',
    include: [{ model: OrderItem, as: 'items', include: [{ model: Product, as: 'product' }] }],
  }],
});
customers[0].orders[0].items.map((i) => i.product.name); // ['Keyboard', 'Mouse']

One query per level

Four levels, four statements: not one per row, and not a join. The count grows
with the depth of the include, never with the number of rows, which is why
LIMIT 20 on the parent still means twenty users.

[!NOTE]
Key lists are not de-duplicated before they are sent: IN (1,2,2,1) above has
one entry per order item. The database treats the duplicates as one value, so
the result is right; the list just grows with the child rows.

What gets attached

Results are attached under the association's alias, and mirrored onto
dataValues:

Association	Attached value
`belongsTo`, `hasOne`	a single instance, or `null`
`hasMany`, `belongsToMany`	an array (empty when there are no children)

const customers = await User.findAll({ include: [{ model: Order, as: 'orders' }] });
customers[0].orders;      // Order[]
const placed = await Order.findAll({ include: [{ model: User, as: 'customer' }] });
placed[0].customer;       // User | null

Per-association direction

The key lives on the parent, so Prorm collects the foreign keys it already has
and looks up the targets by primary key.

await Order.findAll({ include: [{ model: User, as: 'customer' }] });

The key lives on the child.

await User.findAll({ include: [{ model: Order, as: 'orders' }] });

The junction table is queried first, then the targets by the ids it produced.

const products = await Product.findAll({ include: [{ model: Tag, as: 'tags' }] });
products.map((p) => [p.name, p.tags.map((t) => t.name)]);
// [['Keyboard', ['peripherals', 'sale']], ['Mouse', ['peripherals']]]

Filtering includes restrict the parents

An include carrying where, or marked required: true, means inner join
semantics: parents with no matching child are dropped from the result. Because
that must happen before LIMIT, Prorm resolves the matching parent keys
first and constrains the main query with them.

const users = await User.findAll({
  include: [{ model: Order, as: 'orders', where: { status: 'pending' } }],
});
users.map((u) => [u.name, u.orders.length]); // [['Ada', 1]]

required: true does the same thing without a predicate: "only users that
have at least one order". If no parent can qualify, findAll short-circuits and
returns [] without running the main query:

await User.findAll({ include: [{ model: Order, as: 'orders', where: { status: 'refunded' } }] });
// -> SELECT * FROM "orders" WHERE "status" = 'refunded'    (and nothing else)

A parent limit applies after the filter, so it counts qualifying parents:

await User.findAll({ limit: 1, include: [{ model: Order, as: 'orders', where: { status: 'paid' } }] });

[!WARNING]
Step 1 is not restricted to any parents: it reads every child row that
matches the include's where. With required: true and no where, it is
SELECT * FROM "orders", the whole child table. On a large child table,
give the include a selective where, or query the child model first and pass
the parent ids yourself.

Parents with no match: anti-joins

antiJoin: true (also spelled exclude: true or joinType: 'anti') inverts
the filter: it keeps the parents that have no matching child. "Users who
have never ordered":

const neverOrdered = await User.findAll({
  include: [{ model: Order, as: 'orders', antiJoin: true }],
});
neverOrdered.map((u) => u.name); // ['Linus']

antiJoinType ('notExists' | 'notIn' | 'leftJoinIsNull') is accepted and
ignored: all three are ways to write the same question in one statement, and
this loader answers it with a separate query and a NOT IN.

joinType: 'semi' filters the parents like required: true — only parents
with at least one matching child are returned — but the children are not
fetched or attached: the query asks only whether a match exists.

$alias.column$

A where key of the form $alias.column$ names a column on an included
association. It is lifted onto that include, which makes it a parent-restricting
filter with the same three-statement shape as above:

await User.findAll({
  include: [{ model: Order, as: 'orders' }],
  where: { '$orders.status$': 'paid' },
});

Naming an alias you did not include throws an AssociationError telling you to
add the include, rather than dropping the condition silently:

AssociationError: where key '$items.status$' refers to 'items', which is not included
in this query. Add `include: [{ model: ..., as: 'items' }]`.

Include options

Option	Honoured	Effect
`model`	yes	The model to load. Required — an entry without one is skipped.
`as`	yes	Which association to use when several link the same two models.
`where`	yes	Filters the children and restricts the parents (see above).
`attributes`	yes	Column list for the child query.
`order`	yes	`ORDER BY` on the child query.
`required`	yes	Inner-join semantics.
`antiJoin` / `exclude` / `joinType: 'anti'`	yes	Keep only parents with no match (see above).
`include`	yes	Nested includes, to any depth.
`limit` / `offset`	yes	A window per parent, not across the whole result — see below.
`{ all: true }`	yes	Expands to one include per association. `{ all: 'alias' }` and `{ all: true, nested: true }` too.
`separate`	n/a	Every include is already a separate query.

await User.findAll({
  include: [{
    model: Order,
    as: 'orders',
    attributes: ['id', 'total'],
    order: [['total', 'DESC']],
  }],
});

`limit` / `offset` are a per-parent window

"The 3 most recent orders for each user" is what you almost always mean, and it
is what you get:

await User.findAll({
  include: [{ model: Order, as: 'orders', order: [['id', 'DESC']], limit: 3 }],
});

Because one query serves every parent, the LIMIT cannot simply go into that
statement — LIMIT 3 there would return three rows in total and leave every
parent but the first empty. So the database applies the ORDER BY (the
window is over the ordering you asked for, not insertion order) and the slice
happens per parent as rows are grouped onto their parents:

[!WARNING]
That is correct, but it does not reduce what crosses the wire: every
matching child is still fetched and then sliced in memory. For "the latest
three orders" of ten thousand users with long histories, query the child
model directly instead.

One case does push down. With exactly one parent row — findByPk/findOne
plus an include, the common shape — the shared query's window is the
per-parent window, so it becomes real SQL, for hasMany only:

const ada = await User.findByPk(1, {
  include: [{ model: Order, as: 'orders', order: [['id', 'DESC']], limit: 3 }],
});

belongsToMany never pushes down: the junction means fetched rows and attached
array elements are not one-to-one, so a SQL LIMIT would count the wrong thing.

On a to-one include (belongsTo / hasOne) a window is meaningless; it is
ignored and reported rather than silently applied. A limit/offset that is
not a non-negative integer raises an AssociationError instead of being coerced.

`{ all: true }`

await Order.findAll({ include: [{ all: true }] });              // every association
await User.findAll({ include: [{ all: true, nested: true }] }); // and theirs, recursively

Order has two associations, so { all: true } is two include queries:

Each entry expands to one concrete include per association before anything is
loaded, so the result is identical to writing them out by hand — one query per
association. Options on the entry carry onto every expanded include.

Recursion is bounded two ways: a depth cap of 10, and a per-branch record of the
models already expanded. The chain stops re-expansion, not the association
itself, so a circular User ⇄ Order graph settles instead of growing
exponentially. An unknown { all: 'alias' } throws.

[!CAUTION]
{ all: true, nested: true } is convenient in a REPL and expensive in a
request. On this small schema one User.findAll() already runs seven
statements: every association of every reachable model, including the way
back to users through Order.customer. Name the includes you need.

The seven statements

{ all: true } composes with required: true — expansion now happens before
the parent-filtering pass, so every expanded association is required and the
result matches writing the includes out by hand:

await User.findAll({ include: [{ all: true, required: true }] });
// -> SELECT * FROM "users" WHERE "id" IN (…)   — only parents with matches

Association scopes

A scope declared on the association is a fixed condition applied every time
that association is traversed. The include's own where wins where the two
overlap.

User.hasMany(Order, { foreignKey: 'userId', as: 'paidOrders', scope: { status: 'paid' } });

const users = await User.findAll({ include: [{ model: Order, as: 'paidOrders' }] });
users.map((u) => [u.name, u.paidOrders.length]); // [['Ada', 1], ['Grace', 1], ['Linus', 0]]

With two associations between User and Order, an include without as is
now ambiguous and throws:

AssociationError: User has 2 associations to Order (orders, paidOrders).
Disambiguate with `include: [{ model: Order, as: 'orders' }]`.

Counting instead of loading

When you only need the size of a collection, withCount avoids building
instances for every child:

const users = await User.findAll({ withCount: ['orders'] });
users.map((u) => [u.name, u.ordersCount]); // [['Ada', 2], ['Grace', 1], ['Linus', 0]]

It runs one query per association, not per parent:

[!TIP]
The rows are tallied in JavaScript, so this saves instance construction and
round trips but still transfers the child rows. For a large child table, an
aggregate query against the child model is cheaper.

belongsTo counts are resolved without any query at all — the answer is 0 or 1
depending on whether the parent's foreign key is null.

Lazy alternatives

When you have a single instance in hand, the association accessors are usually
clearer than an include:

const user = await User.findByPk(1);
const orders = await user.getOrders();          // findAll on Order, scoped to this user
const n      = await user.countOrders();
await user.addOrder(order);                     // link an existing row
await user.createOrder({ status: 'pending' });  // insert one already linked

These accessors are generated from the association alias — see
Associations for the full naming rules.

[!NOTE]
src/models/model.ts also defines generic getRelated(alias) / setRelated /
createRelated / hasRelated helpers, but they live on the unreachable
createModel() factory (see Streaming),
not on the instances define() / addModel() produce. Use the generated
accessors above.

Reach for include when you have many parents; reach for the accessors when
you have one.

Errors you may hit

Error	Cause
`AssociationError: X has no association to Y`	The association was never declared, or is declared in the other direction.
`AssociationError: X has N associations to Y (…)`	Ambiguous — pass `as` to pick one.
`AssociationError: … has no association to Y aliased 'z'`	The `as` does not match any association's effective alias.
`AssociationError: where key '$z.col$' refers to 'z', which is not included …`	A $alias.column$ key names an alias you did not include.
`EagerLoadError`	The child query itself failed; the cause is attached.

Next: Validation — rejecting bad data before it reaches the database.

Associations — declaring relationships

Querying — the rest of FindOptions

Scopes

Query optimization

Separate queries, not JOINs#

One query per level#

What gets attached#

Per-association direction#

Filtering includes restrict the parents#

Parents with no match: anti-joins#

$alias.column$#

Include options#

limit / offset are a per-parent window#

{ all: true }#

Association scopes#

Counting instead of loading#

Lazy alternatives#

Errors you may hit#

Related reading#