how to paginate with search across multiple fields

[spark hound]

e.g

const users = defineTable({
  organizationId: v.id('organizations'),
  name: v.string(),
  phoneNumber: v.string(),
})
  .index('organizationId', ['organizationId'])
  .index('phoneNumber', ['phoneNumber'])
  .index('name', ['name'])
  .searchIndex('search_name', {
    searchField: 'name',
    filterFields: ['organizationId'],
  })
  .searchIndex('search_phoneNumber', {
    searchField: 'phoneNumber',
    filterFields: ['organizationId'],
  })

for input organizationId and searchTerm (optional)
here i want to return the first 25 users where organizationId is the input organizationId
and name or phonenumber contains the input searchTerm

[inner echo]

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

---

To paginate search results across multiple fields (e.g., searching both name and phoneNumber), Convex currently does not support a single search index spanning multiple fields. Instead, you need to create separate search indexes for each field (as you have done), run separate queries for each, and then merge the results in your query function.

Here’s how you can approach this:

1. Run two search queries: One using the search_name index and one using the search_phoneNumber index, both filtered by organizationId and the search term.
2. Merge the results: Combine the results from both queries, deduplicate users (since a user could match both fields), and paginate the merged list.

Important limitations:

• Pagination is tricky: Since you’re merging two paginated sources, you’ll need to fetch enough results from each to fill your page, and you may need to over-fetch and then trim to the desired page size.
• There is no built-in way to paginate across merged search results with cursor-based pagination; you’ll need to implement offset-based pagination (e.g., skip/take) after merging, which is less efficient for large datasets.

Example approach:

export const searchUsers = query({
  args: {
    organizationId: v.id("organizations"),
    searchTerm: v.optional(v.string()),
    page: v.optional(v.number()), // for offset-based pagination
    pageSize: v.optional(v.number()),
  },
  handler: async (ctx, args) => {
    const pageSize = args.pageSize ?? 25;
    const offset = (args.page ?? 0) * pageSize;

    if (!args.searchTerm) {
      // No search term: just paginate by organizationId
      return await ctx.db
        .query("users")
        .withIndex("organizationId", (q) =>
          q.eq("organizationId", args.organizationId)
        )
        .order("desc")
        .take(pageSize, offset);
    }

// Search by name
const byName = await ctx.db
.query("users")
.withSearchIndex("search_name", (q) =>
q
.search("name", args.searchTerm!)
.eq("organizationId", args.organizationId)
)
.take(1024); // fetch up to 1024 (max allowed)

// Search by phoneNumber
const byPhone = await ctx.db
  .query("users")
  .withSearchIndex("search_phoneNumber", (q) =>
    q
      .search("phoneNumber", args.searchTerm!)
      .eq("organizationId", args.organizationId)
  )
  .take(1024);

// Merge and deduplicate by _id
const merged = [
  ...byName,
  ...byPhone.filter(
    (user) => !byName.some((u) => u._id.equals(user._id))
  ),
];

// Paginate merged results
return merged.slice(offset, offset + pageSize);

},
});

**Caveats:**
- This approach uses offset-based pagination, not cursor-based, so it may not be as efficient for very large datasets.
- If you expect more than 1024 matches in either field, you’ll need to handle that limitation (Convex’s `.take()` and `.collect()` are capped at 1024 results) [see: Full Text Search docs](<https://docs.convex.dev/search/text-search#retrieving-results-and-paginating>).

**References:**
- [Discord: Searching two columns](<https://discord.com/channels/1019350475847499849/1356037921332924436>)
- [Full Text Search docs](<https://docs.convex.dev/search/text-search>)
- [Stack: Denormalizing a field for search](<https://stack.convex.dev/triggers#denormalizing-a-field>)

If you need true cursor-based pagination across merged search results, that is not currently supported natively in Convex. For more advanced use cases, consider denormalizing your data (e.g., combining name and phoneNumber into a single field via a trigger) and indexing that field, but this adds complexity and maintenance overhead.

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

@spark hound Kapa.ai is still learning and improving, please let me know how I did by reacting below.

[inner echo]

Let me check the knowledge base for you again. One moment! :books:

Sorry @spark hound, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist:

Perhaps try again in 15mins?

[spark hound]

@inner echo for name eg. convex
if the input searchTerm is "on" i want it to return this, but its not

[inner echo]

Let me check the knowledge base for you again. One moment! :books:

---

If your user's name is "convex" and the input searchTerm is "on", Convex's full text search will not return "convex" as a match. This is because Convex's full text search uses a tokenizer that splits text on whitespace and punctuation, and only matches whole words or prefixes of words, not substrings within words.

For example:

• Searching for "con" would match "convex" (because "con" is a prefix of "convex").
• Searching for "on" would not match "convex" (because "on" is not a prefix of "convex" and not a standalone word in "convex").

This behavior is described in the documentation:

Text is tokenized using Tantivy's SimpleTokenizer, which splits on whitespace and punctuation.
...
The behavior of search incorporates prefix matching rules.

And:

In your search queries, the final search term has prefix search enabled, matching any term that is a prefix of the original term. For example, the expression search("body", "r") would match the documents:

• "rabbit"
• "send request"

Fuzzy search matches are deprecated. After January 15, 2025, search results will not include "snake" for a typo like "stake" Search Behavior.

So, substring matches (like "on" in "convex") are not supported by Convex full text search. Only prefix and whole word matches are supported.

If you need substring matching, you would need to implement custom filtering in your query logic after fetching results, but this is not efficient or scalable for large datasets and is not supported natively by Convex search indexes.

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

@spark hound Kapa.ai is still learning and improving, please let me know how I did by reacting below.

[spark hound]

@inner echo can you create a custom filtering function

[inner echo]

Let me check the knowledge base for you again. One moment! :books:

---

Yes, you can create a custom filtering function in Convex by fetching documents and then filtering them in TypeScript/JavaScript. This is useful when the built-in filter or search capabilities are not expressive enough for your needs, such as when you want to perform substring matching (e.g., checking if "on" is contained anywhere in "convex").

Here’s how you can do it:

1. Fetch the relevant documents (e.g., by organizationId).
2. Filter them in JavaScript using your custom logic (e.g., includes for substring matching).

Example:

export const searchUsersCustom = query({
  args: {
    organizationId: v.id("organizations"),
    searchTerm: v.optional(v.string()),
  },
  handler: async (ctx, args) => {
    // Fetch all users in the organization
    const users = await ctx.db
      .query("users")
      .withIndex("organizationId", (q) =>
        q.eq("organizationId", args.organizationId)
      )
      .collect();

    // If no searchTerm, return all users
    if (!args.searchTerm) return users;

    // Custom substring filter (case-insensitive)
    const term = args.searchTerm.toLowerCase();
    return users.filter(
      (user) =>
        user.name.toLowerCase().includes(term) ||
        user.phoneNumber.toLowerCase().includes(term)
    );
  },
});

Caveats:

• This approach fetches all matching documents before filtering, which may be inefficient for large datasets.
• For scalable solutions, use indexes and built-in search where possible, but for advanced or substring matching, this is the recommended workaround in Convex today Advanced filtering techniques Stack: Filter too complex? Do it in TypeScript.

If you need to paginate, you can apply .slice(offset, offset + pageSize) to the filtered array.

Let me know if you need a paginated example!

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