Best way to take relation field

[worldly raft]

What's the best way to take a "relation" field?' say i have post table with authorId and i need to show the authorName for each post, how do you write the convex method?

[red seal]

Hi @worldly raft. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

---

The standard pattern in Convex is to query your posts, then use ctx.db.get to fetch the related author for each post inside a Promise.all (or asyncMap). Here's how it looks:

import { query } from "./_generated/server";
import { v } from "convex/values";

export const listPostsWithAuthor = query({
  args: {},
  handler: async (ctx) => {
    const posts = await ctx.db.query("posts").collect();
    return Promise.all(
      posts.map(async (post) => {
        const author = await ctx.db.get(post.authorId);
        return {
          ...post,
          authorName: author?.name ?? "Unknown",
        };
      })
    );
  },
});

This is the idiomatic Convex approach — fetch the list, then do a db.get per related document in parallel via Promise.all. [Loading users by ID]

A note on performance

It may seem like many DB calls compared to a SQL JOIN, but Convex already handles caching under the hood, so these parallel db.get calls are efficient in practice. [Discord thread]

Using convex-helpers for cleaner code

If you want a more readable abstraction, the convex-helpers package provides a getManyFrom helper for back-references and getAll for direct ID lookups:

import { getAll } from "convex-helpers/server/relationships";

const posts = await ctx.db.query("posts").collect();
const authorIds = posts.map((p) => p.authorId);
const authors = await getAll(ctx.db, authorIds);
// authors[i] corresponds to posts[i]

[Relationship Helpers]

Note: getAll returns null in place of any document that doesn't exist. Use getAllOrThrow if you want an exception instead.

---

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:

@worldly raft Kapa.ai is still learning and improving, please let me know how I did by reacting below.

[worldly raft]

@red seal and if i have pagination also?

export const list = query({
  args: {
    tenantId: v.id("tenants"),
    paginationOpts: paginationOptsValidator,
    kind: v.optional(ruleKindValidator),
    isActive: v.optional(v.boolean()),
    search: v.optional(v.string()),
  },
  handler: async (
    ctx,
    { tenantId, kind, search, isActive, paginationOpts },
  ) => {
    await requireTenantAccess(ctx, tenantId);

    // Stage 1: Pick the table to query.
    const rulesQuery: QueryInitializer<DataModel["rules"]> =
      ctx.db.query("rules");

    if (search !== undefined) {
      return await rulesQuery
        .withSearchIndex("search_rules", (q) => {
          const base = q.search("name", search).eq("tenantId", tenantId);
          return kind !== undefined
            ? isActive !== undefined
              ? base.eq("kind", kind).eq("isActive", isActive)
              : base.eq("kind", kind)
            : isActive !== undefined
              ? base.eq("isActive", isActive)
              : base;
        })
        .paginate(paginationOpts);
    }

    // Stage 2: Pick the index to use.
    let indexedQuery: Query<DataModel["rules"]> = rulesQuery;

    if (kind !== undefined) {
      indexedQuery = rulesQuery.withIndex("by_tenant_kind_active", (q) => {
        const base = q.eq("tenantId", tenantId).eq("kind", kind);
        return isActive !== undefined ? base.eq("isActive", isActive) : base;
      });
    } else {
      indexedQuery = rulesQuery.withIndex("by_tenant_active", (q) => {
        const base = q.eq("tenantId", tenantId);
        return isActive !== undefined ? base.eq("isActive", isActive) : base;
      });
    }

    // Stage 3: Apply ordering.
    let orderedQuery: OrderedQuery<DataModel["rules"]> = indexedQuery;
    orderedQuery = indexedQuery.order("asc");

    const rules = await orderedQuery.paginate(paginationOpts);

    return rules;
  },
});

[red seal]

Let me check the knowledge base for you again. One moment! :books:

---

To add the authorName join to a paginated query, you transform the page array of the result returned by .paginate() before returning it. The key is that .paginate() returns an object with a page property (the array of documents), and you can map over that array to enrich each document with related data.

Here's how to apply it to your query:

const paginatedRules = await orderedQuery.paginate(paginationOpts);

return {
  ...paginatedRules,
  page: await Promise.all(
    paginatedRules.page.map(async (rule) => {
      const author = await ctx.db.get(rule.authorId);
      return {
        ...rule,
        authorName: author?.name ?? "Unknown",
      };
    })
  ),
};

And similarly for the search branch:

if (search !== undefined) {
  const paginatedRules = await rulesQuery
    .withSearchIndex("search_rules", (q) => { /* ... */ })
    .paginate(paginationOpts);

  return {
    ...paginatedRules,
    page: await Promise.all(
      paginatedRules.page.map(async (rule) => {
        const author = await ctx.db.get(rule.authorId);
        return {
          ...rule,
          authorName: author?.name ?? "Unknown",
        };
      })
    ),
  };
}

The pattern is: spread the pagination result (...paginatedRules) to preserve the cursor and isDone/continueCursor fields, then replace page with your transformed array. [Paginated Queries]

This works because Convex explicitly supports transforming the page property of the paginated result before returning it, while keeping the rest of the pagination metadata intact.

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: