Nest Joins | Convex Community | Page 1

calm escarp Aug 25, 2025, 6:30 PM

#

How to make nested joins using indices in convex?

spring aspenBOT Aug 25, 2025, 6:30 PM

#

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

#

To perform nested joins in Convex, you compose multiple queries, each using indices for efficient lookups, and nest them in your handler logic. Convex does not have a built-in SQL-like join operator, but you can achieve nested joins by chaining queries and using Promise.all or streaming helpers.

Example: Nested Joins with Indices

Suppose you have three tables: users, orders, and products. You want to fetch users, their orders, and for each order, the associated products.

Step 1: Query the first table using an index

const users = await ctx.db.query("users")
  .withIndex("by_status", q => q.eq("status", "active"))
  .collect();

Step 2: For each user, query related orders using an index

const usersWithOrders = await Promise.all(users.map(async (user) => {
  const orders = await ctx.db.query("orders")
    .withIndex("by_user", q => q.eq("userId", user._id))
    .collect();
  return { ...user, orders };
}));

Step 3: For each order, query related products using an index

const usersWithOrdersAndProducts = await Promise.all(usersWithOrders.map(async (user) => {
  const ordersWithProducts = await Promise.all(user.orders.map(async (order) => {
    const products = await ctx.db.query("products")
      .withIndex("by_order", q => q.eq("orderId", order._id))
      .collect();
    return { ...order, products };
  }));
  return { ...user, orders: ordersWithProducts };
}));

This pattern can be extended to any depth of nesting. Each level uses an index for efficient querying, and you combine the results in JavaScript.

Using Streams for Nested Joins

#

For more scalable or paginated nested joins, you can use the streaming helpers (stream, flatMap, map) from convex-helpers. For example, you can use flatMap to join each parent record to a stream of child records, and then further flatMap or map for deeper nesting. See the Streamed Joins (flatMap) section for details and code examples.

Convex’s approach is to write these joins in TypeScript, using indices for each step, rather than a declarative SQL join. This gives you full flexibility and type safety, and ensures each query is efficient by leveraging the appropriate index at each level. For more, see One-to-Many Join and Composing Patterns.

If you need paginated or reactive nested joins, use the streaming helpers and chain flatMap and map as needed, always specifying the index fields for each join level.

Summary:

Use .withIndex() for each join step.
Compose queries in JavaScript/TypeScript, nesting as needed.
Use Promise.all for parallelism, or streaming helpers for pagination and reactivity.
There is no SQL-style join, but you can nest as deeply as you need using this pattern.

If you need a concrete code example for your specific schema, let me know!

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

#

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

calm escarp Aug 25, 2025, 6:38 PM

#

@spring aspen How woul.d i do it with pagination too?

spring aspenBOT Aug 25, 2025, 6:38 PM

#

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

#

To perform nested joins with pagination in Convex, you should use the streaming helpers from the convex-helpers package, specifically stream, flatMap, and paginate. This approach allows you to efficiently join across multiple tables and paginate the combined results.

How to Do Nested Joins with Pagination

1. Use stream to create a stream for your primary table.
2. Use flatMap to join to the next table, specifying the index fields.
3. Repeat flatMap for deeper nesting as needed.
4. Call .paginate(args.paginationOpts) on the final stream.

Example: One-to-Many Join with Pagination

Suppose you want to fetch all messages in channels a user is a member of, paginated:

const memberships = stream(ctx.db, schema).query("channelMemberships")
  .withIndex("user", q => q.eq("userId", "Bob"));

const messages = memberships.flatMap(async (membership) =>
  stream(ctx.db, schema).query("messages")
    .withIndex("channel", q => q.eq("channelId", membership.channelId)),
  ["channelId"],
);

const results = await messages.paginate(args.paginationOpts);

This paginates the joined results across all channels the user is a member of, using the specified index for efficiency.
See: One-to-Many Join with Pagination

Example: Deeper Nesting

You can nest flatMap calls for deeper joins. For example, to join memberships → messages → reactions:

const memberships = stream(ctx.db, schema).query("channelMemberships")
  .withIndex("user", q => q.eq("userId", args.userId));

const messages = memberships.flatMap((membership) =>
  stream(ctx.db, schema).query("messages")
    .withIndex("channel", q => q.eq("channelId", membership.channelId)),
  ["channelId"],
);

#

const messagesWithReactions = messages.flatMap((message) =>
stream(ctx.db, schema).query("reactions")
.withIndex("message", q => q.eq("messageId", message._id)),
["messageId"],
);

const results = await messagesWithReactions.paginate(args.paginationOpts);

[See: Composing Patterns](<https://stack.convex.dev/translate-sql-into-convex-queries#composing-patterns>)

---

**Key Points:**
- Use `stream` and `flatMap` for each join step.
- Specify the index fields for each join.
- Call `.paginate()` on the final stream to paginate the nested join results.
- This approach is efficient and leverages indices at every step.

If you need to join more than two tables, just keep chaining `flatMap` calls before paginating.  
For more details and examples, see [Merging Streams of Convex Data](<https://stack.convex.dev/merging-streams-of-convex-data#streamed-joins-flatmap>).
----------------------
Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:
----------------------

#Nest Joins