AllanA
Convex Community•6mo 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. 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"])
We want to perform a search by title 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);
  },
});


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
Was this page helpful?