Clever Tagline•7d ago

Paginated indexed query with multiple index choices?

I'm querying a contacts table, and need to retrieve contacts by the region they're in. The table contains a region field (an ID from the regions table) and has an index on that field. When finding contacts matching a single region, both normal and paginated queries using this index are working fine. Yesterday I learned that the app needs to support collecting contacts matching multiple regions. With my normal queries, modifying them to support this requirement was pretty easy. I just ran multiple queries in a loop—one matching each region ID—appending the results of each to an array of type Doc<"contacts">, then returned that array. The snag that I'm hitting is with the paginated query. I can't think of a way to alter it similarly so that its results contain contacts matching all passed region IDs. While I could skip the index and just filter manually, there will eventually be thousands of contacts in this table, so I'd like to find a more optimized solution if at all possible.

5 Replies

Convex Bot•7d ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

Clever TaglineOP•7d ago

I might have just rubberducked this. While finishing my original post I was thinking, "I wonder if there's a way to merge queries," so I searched the docs for "merge" and found this Stack article: https://stack.convex.dev/merging-streams-of-convex-data If I'm reading it correctly, I could stream the queries for each region, merge them, then return the pagination of the result. Something like this:

import { stream, mergedStream } from "convex-helpers/server/stream";
import schema from "./schema";

export const getRegionContacts = query({
  args: {
    regions: v.array(v.id("regions")),
  },
  handler: async (ctx, args) => {
    const contactStreams = new Array() // figure out the stream type
    for (const regionId of args.regions) {
      contactStreams.push(await stream(ctx.db, schema)
        .query("contacts")
        .withIndex("by_region", q => q.eq("region", region))
        .order("desc")
        .collect()
      )
    }
    return mergedStream(contactStreams, ["_creationTime"]).paginate(args.paginationOpts)
  }
})

import { stream, mergedStream } from "convex-helpers/server/stream";
import schema from "./schema";

export const getRegionContacts = query({
  args: {
    regions: v.array(v.id("regions")),
  },
  handler: async (ctx, args) => {
    const contactStreams = new Array() // figure out the stream type
    for (const regionId of args.regions) {
      contactStreams.push(await stream(ctx.db, schema)
        .query("contacts")
        .withIndex("by_region", q => q.eq("region", region))
        .order("desc")
        .collect()
      )
    }
    return mergedStream(contactStreams, ["_creationTime"]).paginate(args.paginationOpts)
  }
})

Am I interpreting that correctly?

Merging Streams of Convex data

New convex-helpers are available now for fetching streams of documents, merging them together, filtering them them out, and paginating the results. Wi...

lee•7d ago

Looks good to me. Great that you found it!

Clever TaglineOP•7d ago

Me too. It's working perfectly! Thanks for adding those stream features! @Lee Just discovered that using .withSearchIndex on a stream is indicating a type error with the search filter: Parameter 'q' implicitly has an 'any' type. FWIW, in this particular case it's not a paginated query. I was using my original method of running multiple queries and adding all of the results to an array, but I thought I'd try to optimize it using streams and ran into this issue.

lee•6d ago

at runtime it would throw an error so i'm kind of glad typescript complains withSearchIndex doesn't work on streams (i think @ian was talking about that elsewhere)

Paginated indexed query with multiple index choices?

Did you find this page helpful?