#Complex query

5 messages · Page 1 of 1 (latest)

finite cypress Nov 1, 2025, 12:04 AM

Hey. We have a table called contents, which is:

export const ContentStatus = v.union(
    v.literal("Available"),
    v.literal("Inbound")
);
export const contentSchema = {
    tenantId: v.id("tenants"),
    assetId: v.id("assets"),
    serialNumber: v.optional(v.string()),
    ownerId: v.optional(v.union(v.id("partners"), v.null())),
    quantity: v.number(),
    location: v.union(
        v.object({ type: v.literal("location"), id: v.id("locations") }), // inside a location
        v.object({ type: v.literal("content"), id: v.id("contents") }), // inside another content
    ),
    ultimateLocationId: v.id("locations"), // the ultimate location
    status: ContentStatus,
};

This table will have 100k-500k rows. We want to be able to do queries by multiple filters. For example based on ownerId and ultimateLocation and status. But the same in all the combinations, like Available or Inbound at ultimateLocation.
Is this possible while staying within convex limits? We know about convex-helpers, streams, aggregates and so. Maybe with aggregates can we pre-calculate stuff or how should we attack this problem?

mint ploverBOT Nov 1, 2025, 12:04 AM

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

Yes, you can support complex queries with multiple filters (e.g., by ownerId, ultimateLocationId, and status, in any combination) in Convex, but there are important considerations and trade-offs, especially at your scale (100k–500k rows).

1. Use Multi-Field Indexes for Efficient Filtering

Convex queries are most efficient when you use indexes. For your use case, you should define multi-field indexes that match your most common query patterns. For example:

contents: defineTable({
  // ...fields...
})
  .index("by_owner_ultimateLocation_status", ["ownerId", "ultimateLocationId", "status"])
  .index("by_ultimateLocation_status", ["ultimateLocationId", "status"])
  .index("by_status", ["status"])
  // Add other combinations as needed

This allows you to use .withIndex(...) in your queries, which will efficiently jump to the relevant range of documents, avoiding full table scans. If you only use .filter(...) without an index, Convex will scan the entire table, which is not scalable at your data volume and will hit Convex's document scan limits (see here for details).

2. Dynamic Query Building

If your filters are dynamic (sometimes filtering by owner, sometimes not), you can build your query in stages and pick the most selective index available at runtime. See the dynamic query builder pattern for how to do this in TypeScript. If you can't cover all combinations with indexes, you can use an index for the most selective field and then post-filter in TypeScript, but this is only scalable if the index narrows down the result set enough.

3. Aggregates for Pre-Calculation

If you need to show counts or sums (e.g., "number of Available items at a location"), Convex's Aggregate component can help. Aggregates are designed to efficiently maintain counts, sums, or other metrics as your data changes, and are especially useful when you need to display totals without scanning large tables. However, you would need to define and maintain an aggregate for each combination of filters you want to support, which can add complexity if you have many combinations (see discussion).

4. Limitations and Trade-offs

If you rely on post-filtering in TypeScript (e.g., using convex-helpers's filter), you will hit Convex's scan limits if the index doesn't narrow the result set enough. This is equivalent to an unindexed SQL query and is not scalable for large tables (see here).
You cannot combine multiple indexes in a single query; you must use a multi-field index or post-filter (see dynamic query builder explanation).

5. Practical Approach

Define multi-field indexes for your most common query patterns.
Use aggregates for counts/sums if you need to display totals efficiently.
Build queries dynamically to use the best available index for each request.
Avoid full table scans—if you must post-filter, ensure the index already narrows the result set to a manageable size.

If you follow these patterns, you can support complex, scalable queries within Convex's limits for your data size.

If you have more combinations than are practical to index or aggregate, you may need to reconsider your data model or query requirements, as this is a fundamental trade-off in Convex and many other databases.

For more details and code patterns, see: