Convex CommunityCCConvex Community
Powered by
AllanA
Convex Community•7mo ago•
8 replies
Allan

Using custom query with typescript filtering and pagination. Performance and limitation issues.

Hello together,

I have a question regarding quering with streams and 
.filterWith
.filterWith
. We have this table structure 

   report: defineTable({
    title: v.optional(v.string()),
    content: v.string(),
    content_summarized: v.string(),
    link: v.optional(v.string()),
    updated_at: v.number(), // UTC timestamp in miliseconds
    date: v.number(), // UTC timestamp in miliseconds,
    tags: v.optional(v.array(v.string())), // TODO linked table
  })
  .index("title", ["title"])
  .index("content", ["content"])
  .index("content_summarized", ["content_summarized"])
   report: defineTable({
    title: v.optional(v.string()),
    content: v.string(),
    content_summarized: v.string(),
    link: v.optional(v.string()),
    updated_at: v.number(), // UTC timestamp in miliseconds
    date: v.number(), // UTC timestamp in miliseconds,
    tags: v.optional(v.array(v.string())), // TODO linked table
  })
  .index("title", ["title"])
  .index("content", ["content"])
  .index("content_summarized", ["content_summarized"])


We want to perform a search by 


We want to perform a search by
title
 and 
 and
content` query, which does a include filtering based on JavaScript. We use this mutation:

export const searchByTitleSimple = query({
  args: { paginationOpts: paginationOptsValidator, searchTermTitle: v.string(), searchTermContent: v.string() },
  handler: async (ctx, args) => {
      let reports_filtered = stream(ctx.db, schema)
      .query("report")
      .withIndex("title")
      .filterWith(async (report) => !!report.title?.toLocaleLowerCase()?.includes(args.searchTermTitle.toLocaleLowerCase()))
      .filterWith(async (report) => report.content.toLocaleLowerCase()?.includes(args.searchTermContent.toLocaleLowerCase()))
    
  
    return await reports_filtered.paginate(args.paginationOpts);
  },
});
export const searchByTitleSimple = query({
  args: { paginationOpts: paginationOptsValidator, searchTermTitle: v.string(), searchTermContent: v.string() },
  handler: async (ctx, args) => {
      let reports_filtered = stream(ctx.db, schema)
      .query("report")
      .withIndex("title")
      .filterWith(async (report) => !!report.title?.toLocaleLowerCase()?.includes(args.searchTermTitle.toLocaleLowerCase()))
      .filterWith(async (report) => report.content.toLocaleLowerCase()?.includes(args.searchTermContent.toLocaleLowerCase()))
    
  
    return await reports_filtered.paginate(args.paginationOpts);
  },
});


I set the index "title" but there are problems which I need to figure out and would like to ask you.
First, Convex blocks if the size of report table is bigger then 16MB
Second, the performance.

I was thinking that there most be a way that filterings needs to be performed in batches so that the search and looping is stopped until the pagination limiting is finished.

What would be the correct convex way to solve this problem?
Thank you
Convex Community banner
Convex CommunityJoin
Join the Convex Discord! Explore Convex, the backend platform that keeps your app in sync.
15,138Members
Resources

Similar Threads

Was this page helpful?
Recent Announcements
Recent Announcements
james

Hi @everyone with everything going on today I forgot to post on discord about our EU launch! We have a region in Dublin now! You can start using it immediately, even for folks on the free plan. https://news.convex.dev/we-finally-got-our-eu-visa/ Stay tuned for more regions but also stay tuned for more infra improvements where we improve latency for teams no matter where their servers are located. Just at the tip of the iceberg re all the optimizations we have lined up.

james · 4d ago

Wayne

Hi @everyone happy Monday. Components Authoring [Challenge](https://www.convex.dev/components/challenge) updates! Meet the second batch of community-approved components **Firecrawl Scrape **- Scrape any URL and get clean markdown, HTML, screenshots, or structured JSON - with durable caching and reactive queries. https://www.convex.dev/components/firecrawl-scrape Built by: Gitmaxd **Durable Agents **- A Convex component for building durable AI agents with an async tool loop. https://www.convex.dev/components/durable-agents Built by: Siegfried **Convex Debouncer** - A server-side debouncing component for debouncing expensive operations like LLM calls, metrics computation, or any heavy processing that should only run after a period of inactivity. https://www.convex.dev/components/debouncer Built by: Ilya **DatabaseChat **- A Convex component for adding natural language database queries to your app. https://www.convex.dev/components/database-chat Built by: Nick **Transloadit** - A Convex component for creating Transloadit Assemblies, handling resumable uploads with status, and persisting status/results in Convex. https://www.convex.dev/components/transloadit Built by: Kvz **Loops** - A Convex component for integrating with Loops.so email marketing platform. https://www.convex.dev/components/loops Built by: Bobby The [challenge](https://www.convex.dev/components/challenge) is now ongoing, so keep building, and we'll keep rewarding. Thanks, everyone!

Wayne · 3w ago

Liz C

Hi everyone! Have you ever wanted to get your hands on some convex swag? Well you're in luck! We just launched our Convex swag store. Check it out here ---> https://store.convex.dev/

Liz C · 3w ago

Similar Threads

query filtering vs js filtering performance
erquhartEerquhart / support-community
3y ago
Pagination, sorting and filtering
fedeFfede / support-community
4mo ago
offset-based pagination with filtering
noob saibotNnoob saibot / support-community
11mo ago
How to implement pagination with join and filtering
noob saibotNnoob saibot / support-community
15mo ago