Complex queries in Convex

[pastel agate]

How would you handle complex filtering in Convex queries? In a way that could support filtering and ordering on multiple columns with text search, like features you would want to have on a table.

I saw streams were a possible solution, but then it’s recommended to set a maximum number of rows read, and that could miss rows on indexes that have more rows than your max reads, but the ones that match your filter are way beyond that in your sort order.

[sand palm]

Hi @pastel agate. 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 give you a single “SQL‑style” way to do arbitrary multi‑column filter + sort + text search that always scales; you combine a few patterns, and you choose how much you want to lean on indexes vs. TypeScript filtering.

Here’s what’s available, based on the docs and threads you saw:

---

1. Use indexes for the primary constraints and sort

Convex queries can only use one index or search index per query, and one order. Dynamic query builders work by:

1. Choosing the table.
2. Choosing one index or search index.
3. Choosing the order.
4. Optionally adding post‑filters. [Dynamic query]

Example pattern:

// Stage 1: base table
const tableQuery = ctx.db.query("cards");

// Stage 2: choose index based on main filter
let indexedQuery = tableQuery;
if (args.ownerId) {
  indexedQuery = tableQuery.withIndex("by_owner", q =>
    q.eq("ownerId", args.ownerId),
  );
} else if (args.boardId) {
  indexedQuery = tableQuery.withIndex("by_board", q =>
    q.eq("boardId", args.boardId),
  );
}

// Stage 3: choose order
let orderedQuery = indexedQuery;
if (args.sortBy === "priority") {
  orderedQuery = indexedQuery.order("desc");
} else if (args.sortBy === "createdAt") {
  orderedQuery = indexedQuery.order("desc");
}

// Optional post‑filters (cheap if result set is already small)
if (args.hideArchived) {
  orderedQuery = orderedQuery.filter(q =>
    q.eq(q.field("archived"), false),
  );
}

const page = await orderedQuery.paginate(args.paginationOpts);

Key idea: use indexes to cut down the candidate set as much as possible, then do the rest with filters.

---

2. Complex predicates in TypeScript (convex-helpers)

For anything that’s hard or impossible in Convex’s FilterBuilder (array includes, multi‑field logic, joins, etc.), you can use the filter helper from convex-helpers. It attaches a TypeScript predicate but still supports .paginate, .take, etc. [Complex filters]

import { filter } from "convex-helpers/server/filter";

export const searchCards = query({
  args: { paginationOpts: paginationOptsValidator, term: v.string() },
  handler: (ctx, args) => {
    return filter(
      ctx.db
        .query("cards")
        .withIndex("by_board", q => q.eq("boardId", args.boardId)),
      card =>
        card.title.toLowerCase().includes(args.term.toLowerCase()) ||
        card.description.toLowerCase().includes(args.term.toLowerCase()),
    ).paginate(args.paginationOpts);
  },
});

Important: this still scans the indexed range; it’s equivalent to an unindexed SQL WHERE on that range. [Complex filters recap]

---

3. Text search across multiple fields

You can’t use multiple search indexes in one query, and a search index is both an index and an order, so it can’t be combined with another index or custom order. [Dynamic query]

Patterns from the threads:

• Denormalized “searchText” field: concatenate the fields you want to search and index that once.

  contacts: defineTable({
    firmId: v.id("firms"),
    name: v.string(),
    email: v.string(),
    phone: v.string(),
    searchText: v.string(), // `${name} ${email} ${phone}` (often lowercased)
  }).searchIndex("search_contacts", {
    searchField: "searchText",
    filterFields: ["firmId"],
  });
  ```[[Discord multi-field search](<https://discord.com/channels/1019350475847499849/1385358143437017178>)]

- **Single combined field for title+content**:

  ```ts
  report: defineTable({
    title: v.optional(v.string()),
    content: v.string(),
    title_content_lowercase: v.string(),
  }).searchIndex("search_title_content", {
    searchField: "title_content_lowercase",
  });

Then query with .withSearchIndex("search_title_content", q => q.search("title_content_lowercase", term)). [TS filtering & searchIndex]

If you need exact matches on some fields (e.g. zip, city) and fuzzy on others, you typically:

• Use a search index for the fuzzy part (on a combined field).
• Apply exact filters either:
  • As filterFields on the search index (if they’re simple equality), or
  • As post‑filters in TypeScript.

---

4. Tags / array filters

For array membership (e.g. tags), the recommended scalable pattern is a join table:

posts: defineTable({
  body: v.string(),
  tags: v.array(v.string()),
}),
tagged_posts: defineTable({
  tag: v.string(),
  post: v.id("posts"),
}).index("by_tag", ["tag"]);

Then query tagged_posts by tag and join back to posts. [Tags join table]

This gives you efficient “has tag X” filters that can be combined with other indexed filters on the main table.

---

5. Streams and “max rows read” concerns

Streams (and helpers like paginator, mergedStream) are used to:

• Merge multiple indexed streams (e.g. for OR‑style queries).
• Implement custom pagination/sorting logic. [Merging streams]

But:

• They still iterate documents in index order.
• You’re right that if you cap “rows read” per stream, you can miss matches that are far down the index if your filter is very selective and your sort order doesn’t align with the index.
• There isn’t a built‑in way to guarantee “perfect” global ordering across multiple indexes with arbitrary filters without potentially scanning a lot of data.

Trusted staff explicitly recommend:

• Use indexes for the most common filters/sorts.
• Use streams/helpers when you accept some complexity and potential trade‑offs.
• For very advanced search/sort use cases (multi‑field ranking, complex scoring), consider an external search engine (Algolia, Meilisearch, Typesense). [Sort + search limits; Pagination/sorting thread]

---

6. What this means for a “table‑like” UI

Given all that, a practical pattern for a data‑table with many filters:

1. Pick one “primary” dimension to index and paginate on (e.g. by_owner_createdAt, or a search index if search is primary).
2. Add multi‑field indexes for the most important sort+filter combos (e.g. by_user_priority, by_user_dueDate).
3. Use post‑filters (Convex filter or convex-helpers filter) for:
   • Less common filters.
   • Cross‑table joins.
   • Complex predicates (arrays, multi‑field AND/OR).
4. Use a denormalized search field + single search index for multi‑field text search.
5. Accept that:
   • Anything not covered by an index is effectively a scan over the indexed range.
   • Streams with strict “max rows read” can miss deep matches; they’re a tool, not a guarantee.

There isn’t a documented pattern that gives you “arbitrary SQL‑like WHERE + ORDER BY across many columns with perfect pagination” without these trade‑offs; when you truly need that, the official guidance is to either reshape your data/indexes or offload to a dedicated search system. [Sort + search limits]

If you describe your concrete table schema and the exact filters/sorts you need, I can map them to a specific index + post‑filter strategy using these patterns.

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