CasperLeerink
CasperLeerink12h 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
6 Replies
Convex Bot
Convex Bot12h 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
erquhart11h ago
Is the realistic number of provider ids and quote ids unbounded or is it expected to generally be a lower number?
CasperLeerink
CasperLeerinkOP9h 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
erquhart9h 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
CasperLeerinkOP9h 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
erquhart7h ago
Not with an index, an index on the ids in question would only scan relevant rows

Did you find this page helpful?