# Relations > Load related rows with include and select, count them with count, and use many-to-many relations. URL: https://better-drizzle.com/docs/querying/relations Use `include` and relation `select` to load related rows with one call and a typed nested result. Relations come from Drizzle's `defineRelations`. To filter rows by their relations, see [relation filters](/docs/querying/relation-filters). Relation reads use a batch loader owned by better-drizzle: one root query plus normally one query per requested relation node. Large parent-key sets are split into sequential queries with at most 10,000 link parameters; composite keys also use at most 200 OR branches per query. The batch size also reserves parameters used by filters, scopes, counts, and pagination. If those arguments leave no room for a parent key, the loader throws `INVALID_ARGS`. Duplicate keys are fetched once across all batches, and overlapping query results keep each group once. Ordering, pagination, and grouping still apply per parent. The examples below extend the [getting started](/docs/getting-started) `users → posts` schema with `comments`, `profiles`, a `posts.editorId` column, and a `memberships` junction between `users` and `groups`. ## Defining relations better-drizzle reads relations from the Drizzle instance, so declare them with `defineRelations` and pass the result to `drizzle()`: ```ts title="relations.ts" import { defineRelations } from 'drizzle-orm'; import * as schema from './schema'; export const relations = defineRelations(schema, (r) => ({ users: { posts: r.many.posts(), profile: r.one.profiles({ from: r.users.id, to: r.profiles.userId }), }, posts: { author: r.one.users({ from: r.posts.authorId, to: r.users.id }), comments: r.many.comments(), }, comments: { post: r.one.posts({ from: r.comments.postId, to: r.posts.id }), }, })); ``` ```ts title="db.ts" import { better } from 'better-drizzle'; import { drizzle } from 'drizzle-orm/node-postgres'; import { relations } from './relations'; export const db = drizzle(process.env.DATABASE_URL!, { relations }); export const client = better(db); ``` A foreign key (`.references(...)`) alone is not a relation. Only relations declared in `defineRelations` are available to `include`, `select`, `count`, relation filters, and relation writes. When two relations connect the same pair of tables, give both sides the same `alias`, as Drizzle requires: ```ts export const relations = defineRelations(schema, (r) => ({ posts: { author: r.one.users({ from: r.posts.authorId, to: r.users.id, alias: 'author' }), editor: r.one.users({ from: r.posts.editorId, to: r.users.id, alias: 'editor' }), }, users: { authoredPosts: r.many.posts({ alias: 'author' }), editedPosts: r.many.posts({ alias: 'editor' }), }, })); ``` ## Loading relations Use [`include`](/docs/querying/selecting-fields) to keep the base row and attach relations: **better-drizzle** ```ts const posts = await client.posts.findMany({ include: { author: true }, orderBy: [{ id: 'desc' }], take: 10, }); ``` **Drizzle** ```ts import { desc, eq, getColumns } from 'drizzle-orm'; const latestPosts = await db .select({ ...getColumns(posts), author: getColumns(users) }) .from(posts) .leftJoin(users, eq(posts.authorId, users.id)) .orderBy(desc(posts.id)) .limit(10); ``` Or `select` to also narrow the related fields: **better-drizzle** ```ts const posts = await client.posts.findMany({ select: { id: true, title: true, author: { select: { id: true, name: true } }, }, }); ``` **Drizzle** ```ts import { eq } from 'drizzle-orm'; const rows = await db .select({ id: posts.id, title: posts.title, author: { id: users.id, name: users.name }, }) .from(posts) .leftJoin(users, eq(posts.authorId, users.id)); ``` Every nested relation accepts its own `where`, `orderBy`, `take`, `skip`, `cursor`, `select`, and `include`. Pagination is applied per parent in SQL: **better-drizzle** ```ts const users = await client.users.findMany({ include: { posts: { where: { published: true }, orderBy: { score: 'desc' }, take: 3, select: { title: true, comments: { select: { body: true } }, }, }, }, }); ``` **Drizzle** ```ts import { and, asc, eq, inArray, lte, sql } from 'drizzle-orm'; const userRows = await db.select().from(users); // top 3 published posts per user needs a window function const ranked = db .select({ id: posts.id, authorId: posts.authorId, title: posts.title, rank: sql`row_number() over (partition by ${posts.authorId} order by ${posts.score} desc)`.as('rank'), }) .from(posts) .where( and( eq(posts.published, true), inArray(posts.authorId, userRows.map((user) => user.id)), ), ) .as('ranked'); const postRows = await db .select() .from(ranked) .where(lte(ranked.rank, 3)) .orderBy(asc(ranked.authorId), asc(ranked.rank)); const commentRows = await db .select({ postId: comments.postId, body: comments.body }) .from(comments) .where(inArray(comments.postId, postRows.map((post) => post.id))); // stitch the tree back together and drop the linking columns const usersWithPosts = userRows.map((user) => ({ ...user, posts: postRows .filter((post) => post.authorId === user.id) .map((post) => ({ title: post.title, comments: commentRows .filter((comment) => comment.postId === post.id) .map(({ body }) => ({ body })), })), })); ``` `select` and `include` are mutually exclusive at the same query level. Linking columns needed by the loader are selected internally and removed from the returned payload unless you requested them. ## Relation counts Use `count` inside `include` to return counts without loading the related rows. A selected relation can be `true` or a `{ where }` filter. The result is available under `count`; to-one relations return `0` or `1`. `count` is a reserved key: a relation named `count` cannot be loaded through `include`. See [limitations](/docs/guides/limitations#reserved-names). The old `_count` spelling is a deprecated alias, see [upgrading to 0.4](/docs/guides/upgrading-0-4). **better-drizzle** ```ts const users = await client.users.findMany({ include: { count: { select: { posts: { where: { published: true } }, profile: true, }, }, }, }); // { ..., count: { posts: 3, profile: 1 } } ``` **Drizzle** ```ts import { and, eq, getColumns, sql } from 'drizzle-orm'; const rows = await db .select({ ...getColumns(users), count: { posts: sql`(select count(*) from ${posts} where ${and(eq(posts.authorId, users.id), eq(posts.published, true))})`.mapWith(Number), profile: sql`(select count(*) from ${profiles} where ${eq(profiles.userId, users.id)})`.mapWith(Number), }, }) .from(users); // { ..., count: { posts: 3, profile: 1 } } ``` Counts are projected as correlated subqueries in the SQL that materializes that query level. They do not add a count query per parent row. ## Many-to-many relations Declare many-to-many relations natively with `.through()` on both ends, naming the junction columns: ```ts title="relations.ts" export const relations = defineRelations(schema, (r) => ({ users: { groups: r.many.groups({ from: r.users.id.through(r.memberships.userId), to: r.groups.id.through(r.memberships.groupId), }), }, groups: { users: r.many.users({ from: r.groups.id.through(r.memberships.groupId), to: r.users.id.through(r.memberships.userId), }), }, })); ``` The relation then behaves like any other to-many relation - `include`, `select`, `count`, `some` / `every` / `none`, and `connect` / `disconnect` / `set` all work through it, and you never name the junction table in a query: **better-drizzle** ```ts const users = await client.users.findMany({ include: { groups: true }, }); ``` **Drizzle** ```ts import { eq, getColumns, inArray } from 'drizzle-orm'; const userRows = await db.select().from(users); // walk the junction table by hand const links = await db .select({ userId: memberships.userId, group: getColumns(groups) }) .from(memberships) .innerJoin(groups, eq(memberships.groupId, groups.id)) .where(inArray(memberships.userId, userRows.map((user) => user.id))); const usersWithGroups = userRows.map((user) => ({ ...user, groups: links .filter((link) => link.userId === user.id) .map((link) => link.group), })); ``` ## Relations better-drizzle cannot load Two kinds of Drizzle relations are not supported by the better-drizzle loader: * relations declared with a relation-level `where` (filtered relations) * `one` relations declared through a junction table They are not part of the better-drizzle relation API: loading one with `include` or `select` throws `Relation "x" on "t" cannot be loaded: ...`, and filtering through one in `where` throws `Relation "x" on "t" cannot be filtered: ...`. Sorting by one in `orderBy` throws the same unsupported-relation error. They still work with Drizzle's own relational queries on the underlying Drizzle instance. ## Explain plans `.explain()` executes the root dialect-specific explain and reports relation queries under `deferredRelations`, including each path, cardinality, target table, junction table, filtering, sorting, and pagination flags. ## Rule of thumb * Use `where` to control **which rows qualify** - including by their relations. * Use `select` to control **which fields survive**. * Use `include` to **keep the full row and attach** relations.