#Retrieve Org Data

11 messages · Page 1 of 1 (latest)

elfin relic Dec 14, 2025, 11:51 PM

I have two tables, organizations and organizationMemberships, which are linked by organizations.workosId and organizationMemberships.workosOrganizationId. The organizationMemberships table also includes a workosUserId column, while the organizations table contains a name column. I want to write a query that retrieves the most recent organization memberships, allows filtering by organization name, supports pagination, and returns five results per page.

fair wraithBOT Dec 14, 2025, 11:51 PM

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

You can do this with a paginated query that:

Pages over organizationMemberships (ordered by _creationTime).
Joins each membership to its organization via workosOrganizationId.
Optionally filters by organizations.name.
Uses Convex cursor-based pagination with paginationOpts and usePaginatedQuery.[Paginated queries; One-to-one join]

Assuming:

organizations: defineTable({ workosId: v.string(), name: v.string(), ... })
organizationMemberships: defineTable({ workosOrganizationId: v.string(), workosUserId: v.string(), ...timeStampFields })
organizationMemberships has an index on _creationTime (or you use the default _creationTime ordering)

Convex query (server)

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

export const listMemberships = query({
  args: {
    paginationOpts: paginationOptsValidator,
    // Optional filter by organization name
    orgName: v.optional(v.string()),
  },
  handler: async (ctx, { paginationOpts, orgName }) => {
    // Page over most recent memberships
    const results = await ctx.db
      .query("organizationMemberships")
      .order("desc") // most recent first
      .paginate(paginationOpts); // cursor-based pagination [[Paginated queries](<https://docs.convex.dev/database/pagination>)]

// Join to organizations and apply orgName filter in JS
const pageWithOrgs = (
await Promise.all(
results.page.map(async (membership) => {
const org = await ctx.db
.query("organizations")
.filter((q) =>
q.eq(q.field("workosId"), membership.workosOrganizationId),
)
.first(); // or use an index on workosId if you have one [Filtering]

      if (!org) return null;
      if (orgName && org.name !== orgName) return null;

      return {
        membership,
        organization: org,
      };
    }),
  )
).filter((x) => x !== null);

// Keep the same pagination shape, just replace `page`
return {
  ...results,
  page: pageWithOrgs,
};

},
});


This follows the recommended pattern of paginating first, then transforming the `page` before returning it.[[Transforming results](<https://docs.convex.dev/database/pagination#transforming-results>)]

### React usage (5 results per page)

```tsx
import { usePaginatedQuery } from "convex/react";
import { api } from "../convex/_generated/api";

export function MembershipList({ orgName }: { orgName?: string }) {
  const { results, status, loadMore } = usePaginatedQuery(
    api.organizations.listMemberships,
    {
      orgName: orgName ?? undefined,
    },
    {
      initialNumItems: 5, // 5 memberships per page [[Paginating in React](<https://docs.convex.dev/database/pagination#paginating-within-react-components>)]
    },
  );

  return (
    <div>
      {results?.map(({ membership, organization }) => (
        <div key={membership._id}>
          <div>{organization.name}</div>
          <div>User: {membership.workosUserId}</div>
        </div>
      ))}

<button
onClick={() => loadMore(5)}
disabled={status !== "CanLoadMore"}
>
Load more
</button>
</div>
);
}


If you need the `orgName` filter to be fully index-backed and scalable, you could:

- Add an index to `organizations` on `name`, page organizations by name, then stream/join memberships via `stream` + `flatMap` as in the “streamed joins” pattern, but the knowledge sources only show that pattern conceptually and not specifically for your schema.[[Streamed joins](<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:
----------------------

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

elfin relic Dec 15, 2025, 12:00 AM

@fair wraith im using the zod helper, how can i use paginationOptsValidator with zod

fair wraithBOT Dec 15, 2025, 12:00 AM

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

You don’t pass paginationOptsValidator into Zod — instead, you either:

Use Convex’s validator for the paginationOpts arg, alongside Zod for your other args, or
Use the convex-helpers Zod integration (zCustomQuery / zCustomMutation) which already converts Zod to Convex validators for you.

From the docs:

Convex exposes paginationOptsValidator to validate paginationOpts in args.[server vars; Pagination docs]
convex-helpers/server/zod lets you define args as Zod schemas; they’re converted to Convex validators with zodToConvex before running Zod at runtime.[Zod + Convex]

Option 1: Mixed (Zod for your args, Convex for pagination)

If you’re using withZod-style helpers (the first Zod article), those wrappers validate only the user args and bypass the first { db, ... } context arg.[withZod usage] That pattern doesn’t know about paginationOptsValidator, so the simplest approach is to:

Keep paginationOpts in the Convex args object using paginationOptsValidator
Use Zod inside the handler for finer validation of other fields if needed

Example:

import { v } from "convex/values";
import { query } from "./_generated/server";
import { paginationOptsValidator } from "convex/server"; // Convex
import { z } from "zod";

const orgFilterSchema = z.object({
  orgName: z.string().optional(),
});

export const listMemberships = query({
args: {
paginationOpts: paginationOptsValidator,
// lightweight Convex types for everything else
orgName: v.optional(v.string()),
},
handler: async (ctx, args) => {
// Re-validate/normalize with Zod if you need tighter rules
const { orgName } = orgFilterSchema.parse({
orgName: args.orgName,
});

const results = await ctx.db
  .query("organizationMemberships")
  .order("desc")
  .paginate(args.paginationOpts);

// ... join to organizations & filter by orgName ...
return results;

},
});


Here Zod is used for your semantic logic; `paginationOptsValidator` is used exactly as in the pagination docs, and you don’t try to represent it in Zod.[[Pagination docs](<https://docs.convex.dev/database/pagination>)]

### Option 2: Full Zod args via `convex-helpers/server/zod`

If you’re using `convex-helpers/server/zod` (`zCustomQuery` etc.), those helpers accept a Zod object for `args` and internally **translate it to Convex validators**, including for things like IDs via `zodToConvex`.[[Zod + Convex](<https://stack.convex.dev/typescript-zod-function-validation#using-zod-for-argument-validation-server-side>)]

In that flow, you would:

- Define a Zod schema for `paginationOpts` (matching `PaginationOptions` shape: `{ numItems: number; cursor: string | null }`[[PaginationOptions](<https://docs.convex.dev/api/interfaces/server.PaginationOptions>)])
- Pass that as part of your Zod args object
- Let `zCustomQuery` handle conversion to Convex validators

The exact helper code for handling pagination isn’t shown in the provided sources, so I can’t give a guaranteed‑correct snippet of `zodToConvex`’s internal behavior for `paginationOpts`. The docs only state that:

It turns the Zod validator into a Convex validator using zodToConvex … and runs the Zod validator before the function runs.[Zod + Convex]

Because the knowledge sources don’t show an explicit example of combining paginationOptsValidator with those Zod helpers, I can’t say with certainty that there’s a ready‑made Zod wrapper that reuses paginationOptsValidator directly.

So, today’s safe pattern from the docs is:

Use paginationOptsValidator directly in your Convex args object to type and validate paginationOpts.
Use Zod in parallel (inside your handler, or via wrappers like withZod/zCustomQuery) for your own higher‑level arguments, but don’t try to wrap paginationOptsValidator itself in Zod since the docs don’t show that hook.