Queries and best practices: How to query multiple tables and return object using Convex.dev?

Question

I have a few queries, that seems very large in terms of lines of code, and I was wondering if there is a better way to do this. This example is a get query from multiple tables to then end up returning one object with a lot of information:

export const get = query({
  handler: async (ctx) => {
    const identity = await ctx.auth.getUserIdentity()

    if (!identity) {
      throw new Error("Not authenticated")
    }

    const userId = identity.subject

    const user = await ctx.db
      .query("users")
      .withIndex("by_user", (q) => q.eq("userId", userId))
      .first()

    if (!user) {
      throw new Error("Unauthenticated")
    }

    const teamId = user.activeTeam as Id<"teams">

    if (!teamId) {
      throw new Error("User has no active team")
    }

    const [flows, pipelines] = await Promise.all([
      ctx.db
        .query("flows")
        .withIndex("by_team", (q) => q.eq("teamId", teamId))
        .collect(),
      ctx.db
        .query("pipelines")
        .withIndex("by_team", (q) => q.eq("teamId", teamId))
        .collect(),
    ])

    const getStatuses = pipelines.map((pipeline) =>
      ctx.db
        .query("statuses")
        .withIndex("by_pipeline", (q) => q.eq("pipelineId", pipeline._id))
        .collect()
    )

    const getPriorities = pipelines.map((pipeline) =>
      ctx.db
        .query("priorities")
        .withIndex("by_pipeline", (q) => q.eq("pipelineId", pipeline._id))
        .collect()
    )

    const getTypes = pipelines.map((pipeline) =>
      ctx.db
        .query("types")
        .withIndex("by_pipeline", (q) => q.eq("pipelineId", pipeline._id))
        .collect()
    )

    const [statusesArray, prioritiesArray, typesArray] = await Promise.all([
      Promise.all(getStatuses),
      Promise.all(getPriorities),
      Promise.all(getTypes),
    ])

    const statuses = statusesArray.flat()
    const priorities = prioritiesArray.flat()
    const types = typesArray.flat()

    const pipelineMap = new Map(pipelines.map((p) => [p._id, p]))
    const statusMap = new Map(statuses.map((s) => [s._id, s]))
    const priorityMap = new Map(priorities.map((p) => [p._id, p]))
    const typeMap = new Map(types.map((t) => [t._id, t]))

    const memberPromises = flows.map((flow) =>
      ctx.db
        .query("flowMembers")
        .withIndex("by_flow", (q) => q.eq("flowId", flow._id as Id<"flows">))
        .collect()
    )
    const flowMembers = await Promise.all(memberPromises)

    const allUserIds = flowMembers.flat().map((member) => member.userId)
    const uniqueUserIds = Array.from(new Set(allUserIds))

    const userPromises = uniqueUserIds.map((userId) =>
      ctx.db
        .query("users")
        .withIndex("by_id", (q) => q.eq("_id", userId as Id<"users">))
        .first()
    )
    const users = await Promise.all(userPromises)

    const userMap = new Map(
      users.filter((user) => user !== null).map((user) => [user!._id, user!])
    )

    const flowsExtended = flows.map((flow, index) => {
      const pipeline = pipelineMap.get(flow.pipelineId as Id<"pipelines">)
      const status = statusMap.get(flow.statusId as Id<"statuses">)
      const priority = priorityMap.get(flow.priorityId as Id<"priorities">)
      const type = typeMap.get(flow.typeId as Id<"types">)
      const members = flowMembers[index].map((member) =>
        userMap.get(member.userId as Id<"users">)
      )

      return {
        ...flow,
        pipeline: pipeline || null,
        status: status || null,
        priority: priority || null,
        type: type || null,
        members: members,
      }
    })

    return flowsExtended
  },
})

I heard about the query `convex-helpers/react` but I'm not sure if that can be of any help.

Answer 1

The first thing to note is that you are not limited to a single function for this code. Since a Convex function is just JavaScript you can organize your code like you would any other JavaScript. For example, consider a helper function like this:

async function getUser(ctx: QueryCtx, userId: Id<"users">) {

    const user = await ctx.db
      .query("users")
      .withIndex("by_user", (q) => q.eq("userId", userId))
      .first()

    if (!user) {
      throw new Error("Unauthenticated")
    }

    return user;
}

Now you can call that function in the body of your get query function.

convex-helpers/react doesn't have much to do with this; it's for React hooks. But it is true that you can safely split up query functions like this into multiple query functions on the client side, because Convex provides consistent client views; instead of

  const data = useQuery(api.flows.get, {});

you could split this into

  const flows = useQuery(api.flows.get);
  const users = useQuery(api.flows.getUsers);

and once both pieces of data are loaded, know that they are from the same logical backend timestamp. This is a guarantee you do not get from most backends when fetching from multiple endpoints.

But unless you need different data in different locations, I wouldn't recommend this; if this data makes sense together, then doing a lot of work in one query seems ok.