CasperLeerink
CasperLeerink•2mo ago

Can't wrap my head around paginated queries with advanced filters

I have this complex query:
export const listAvailable = protectedQuery({
args: {
searchQuery: v.optional(v.string()),
cursor: v.optional(v.number()),
},
handler: async (ctx, args) => {
const limit = 5;
const providerServices = await ctx.db
.query("providerServices")
.withIndex("by_provider", (q) => q.eq("providerId", ctx.profile._id))
.collect();
const providerServiceIds = providerServices.map(
(service) => service.serviceId,
);
const quotes = await ctx.db
.query("quotes")
.withIndex("by_provider", (q) => q.eq("providerId", ctx.profile._id))
.filter((q) => q.eq("status", "Pending"))
.collect();
const quotedRequestIds = quotes.map((quote) => quote.requestId);
const serviceRequests = await ctx.db
.query("serviceRequests")
.withIndex("by_creation_time", (q) =>
args.cursor ? q.lte("_creationTime", args.cursor) : q,
)
.order("desc")
.filter((q) => {
return q.and(
q.eq("status", "pending"),
q.inArray("serviceId", providerServiceIds),
q.notInArray("id", quotedRequestIds),
);
})
.take(limit + 1);

let nextCursor: typeof args.cursor = undefined;
if (serviceRequests.length > limit) {
const nextItem = serviceRequests.pop();
nextCursor = nextItem?._creationTime;
}
return {
items: serviceRequests,
nextCursor,
};
},
});
export const listAvailable = protectedQuery({
args: {
searchQuery: v.optional(v.string()),
cursor: v.optional(v.number()),
},
handler: async (ctx, args) => {
const limit = 5;
const providerServices = await ctx.db
.query("providerServices")
.withIndex("by_provider", (q) => q.eq("providerId", ctx.profile._id))
.collect();
const providerServiceIds = providerServices.map(
(service) => service.serviceId,
);
const quotes = await ctx.db
.query("quotes")
.withIndex("by_provider", (q) => q.eq("providerId", ctx.profile._id))
.filter((q) => q.eq("status", "Pending"))
.collect();
const quotedRequestIds = quotes.map((quote) => quote.requestId);
const serviceRequests = await ctx.db
.query("serviceRequests")
.withIndex("by_creation_time", (q) =>
args.cursor ? q.lte("_creationTime", args.cursor) : q,
)
.order("desc")
.filter((q) => {
return q.and(
q.eq("status", "pending"),
q.inArray("serviceId", providerServiceIds),
q.notInArray("id", quotedRequestIds),
);
})
.take(limit + 1);

let nextCursor: typeof args.cursor = undefined;
if (serviceRequests.length > limit) {
const nextItem = serviceRequests.pop();
nextCursor = nextItem?._creationTime;
}
return {
items: serviceRequests,
nextCursor,
};
},
});
Now the q.inArray or q.notInArray does not exist unfortunately. The docs say that you have to do it in javascript, but then you have to use collect() instead of take as otherwise it might filter out the records and end up with an empty page result. Is .collect() method and then filtering in js indeed the right option? In this case it would have to read through the whole table whereas with it would just loop until it has the 6 first rows
10 Replies
Convex Bot
Convex Bot•2mo 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!
erquhart
erquhart•2mo ago
Is the realistic number of provider ids and quote ids unbounded or is it expected to generally be a lower number?
CasperLeerink
CasperLeerinkOP•2mo ago
Lower number Basically providers can offer multiple services, and I only want to query the service requests for the services they offer. And also want to filter out requests for which they already made a quote.
erquhart
erquhart•2mo ago
Is the searchQuery meant to be used in a search index? If the lists of service requests and quotes are small you could just map over the list and get the relations instead of using filtering.
CasperLeerink
CasperLeerinkOP•2mo ago
no you can ignore that the search i mean I'm not sure how that looks? I want to filter by those, I already have the document ids the service request table theoretically can get very big so when doing take(5) since its ordered creation time that should be fast, but if you do collect() it has to loop over the entire table no?
erquhart
erquhart•2mo ago
Not with an index, an index on the ids in question would only scan relevant rows
CasperLeerink
CasperLeerinkOP•2mo ago
For anyone reading this in the future: https://stack.convex.dev/complex-filters-in-convex is what you need 🙂
Using TypeScript to Write Complex Query Filters
There’s a new Convex helper to perform generic TypeScript filters, with the same performance as built-in Convex filters, and unlimited potential.
erquhart
erquhart•2mo ago
The only reason I didn't recommend this is because it changes once you go from prototyping to scale: https://stack.convex.dev/complex-filters-in-convex#optimize-with-indexes
CasperLeerink
CasperLeerinkOP•2mo ago
hmm even with the pagination? I still use an index on the status now, so it will only consider pending, but then considering its ordered by _creationtime, I thought it will just loop through the service request rows until it has the amount specified, lets say your page size is 10, it will only look at the 10+ rows it needs to get the 10 that are matching the filters no? So it wouldn't do a full table scan
erquhart
erquhart•2mo ago
The status index will help for sure. If the performance is working for you, you're good. But whether this is the right approach depends heavily on the exact use case, which is why pagination is mentioned as an escape hatch in the article. The OP use case here basically aligns with the part I linked - posts by tag (eg., array.includes). As the article mentions, "This is the efficient way to look up posts by tag.". There isn't a better perf approach. But for some use cases like yours, the perf difference may be negligible.

Did you find this page helpful?