#Query with `whereIn` in convex

4 messages · Page 1 of 1 (latest)

tardy ridge
#

Following on from my question here:

#1459382529521094707 message

It seems that you have recommended using multiple point queries instead of an array because convex does not support it. Why is this not in the documentation? It seems like this is such a common query (I do it all the time in SQL land).

Isn't this grossly inefficient. Does convex somehow batch queries to prevent the latency/overhead associated with making many multple round trips rather than a single query?

hushed laurelBOT
#

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

#

Convex doesn’t currently support an efficient IN / OR operator on indexes (the moral equivalent of WHERE field IN (...) in SQL). Instead, the recommended pattern is exactly what you saw: run one indexed query per value and then merge the results in TypeScript. [No efficient in; Union]

Why this isn’t “one query” in Convex

Conceptually, an efficient IN on an index is just N separate range lookups on that index. Convex makes that explicit in the API instead of hiding it behind a single whereIn call. The docs call this out as “No efficient in / or” and show the pattern:

messages: defineTable(...).index("author", ["author"]);

async function authoredMessages(author: string) {
  return await ctx.db.query("messages")
    .withIndex("author", q => q.eq("author", author)).order("desc")
    .collect();
}

const allMessages = await Promise.all(["Alice", "Bob"].map(authoredMessages));
const messages = allMessages.flat()
  .sort((a, b) => b._creationTime - a._creationTime);

[Union]

There’s also a QueryStream version that merges multiple indexed streams and paginates them as one logical result. [Union]

Is this grossly inefficient?

According to the Convex team, no—this is the efficient way to do it:

#
  • Each subquery uses an index, so you avoid scanning the whole table.
  • Convex runs these subqueries very close to the storage layer and can parallelize them; you’re not paying N separate client→DB network round trips.
  • This is effectively what a SQL query planner would do under the hood anyway; Convex just exposes it directly. [N+1]

For example, when someone asked about an IN-style query over combinations of regionId and propertyTypeId, support confirmed that looping and issuing multiple indexed queries is the right approach. [IN operator]

Why isn’t there a whereIn helper?

The knowledge sources don’t say explicitly why there isn’t a whereIn helper, only that:

  • Efficient in / or can’t be done as a single index range; it’s inherently multiple queries. [No efficient in]
  • Convex prefers an API that makes “what’s efficient” vs “what’s a scan / union” obvious. [Query syntax]

So today you write the loop yourself (or use helpers like mergedStream), but the underlying work is what a hypothetical whereIn would have to do anyway.

If you need to paginate across the union of multiple indexed queries, the documented pattern is to use stream + mergedStream and then .paginate(...) on the merged stream. [Paginated multi-index; Union streams]

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: