M Zeeshan•4mo ago

How to apply filter before pagination?

In the provided code, why are filter calls applied first to paginate the documents, and then the Predicate function is applied to those paginated documents? What I need: I want to first search the database, and then apply pagination to the filtered results. How can I modify the code to achieve this? Provided Code:

    const results = await filter(ctx.db.query('skills'), (doc) => {
      return (
        doc.is_deleted === false &&
        (args.is_featured === undefined ||
          doc.is_featured === args.is_featured) &&
        (args.is_published === undefined ||
          doc.is_published === args.is_published) &&
        (!args.query || doc.title.includes(args.query))
      );
    }).paginate(args.paginationOpts);

    const results = await filter(ctx.db.query('skills'), (doc) => {
      return (
        doc.is_deleted === false &&
        (args.is_featured === undefined ||
          doc.is_featured === args.is_featured) &&
        (args.is_published === undefined ||
          doc.is_published === args.is_published) &&
        (!args.query || doc.title.includes(args.query))
      );
    }).paginate(args.paginationOpts);

16 Replies

nipunn•4mo ago

this comment has good information about filter (assuming filter is referring to the one from convex-helpers, right?) https://github.com/get-convex/convex-helpers/blob/main/packages/convex-helpers/server/filter.ts#L145 Notably, .paginate occurs on the indexed query to reduce the amount of data read. filter happens afterward to filter the data read. The helper is written so you can write things in any order, but the filter applies at the end. I don't think there's a semantic difference between paginate -> filter and filter -> paginate. They seem like they would both give you paginated filtered results. What about your existing code doesn't work for you?

GitHub

convex-helpers/packages/convex-helpers/server/filter.ts at main · g...

A collection of useful code to complement the official packages. - get-convex/convex-helpers

nipunn•4mo ago

You can use an index to narrow your results before filtering (eg an index on is_deleted, is_featured) - which would improve performance, but semantically you'd still get paginated filtered results.

lee•4mo ago

there is actually a difference as you describe -- filter from convex-helpers applies the filter after paginating, whereas built-in filters apply before paginating. This is intentional because it means the query reads a bounded number of rows from the db so they will never be slow or hit query limits. If you want more results, you can call loadMore. If you want more control, like have a query internally fetch multiple pages and then filter them so it returns a consistent number of results, you can use a different helper https://stack.convex.dev/pagination

Take Control of Pagination

Convex offers robust control over pagination with a powerful function, getPage, enabling complex edge cases. In this article, we go over how to use th...

M ZeeshanOP•4mo ago

@nipunn @lee I experimented and found a working solution for filtring, opting for cursor-based pagination to stay within Convex. Now, I'm encountering a TypeScript error when using .withIndex(). Can you please help me resolve this error and correctly implement .withIndex()?

results = await ctx.db
        .query('skills')
        .withIndex('search_fields', (q) => {
          if (
            args.is_published !== undefined &&
            args.is_featured !== undefined
          )
            return q
              .eq('is_deleted', false)
              .eq('is_published', args.is_published)
              .eq('is_featured', args.is_featured);

          if (args.is_featured !== undefined)
            return q
              .eq('is_deleted', false)
// ERROR : Argument of type '"is_featured"' is not assignable to parameter of type '"is_published"'.
              .eq('is_featured', args.is_featured);

          if (args.is_published !== undefined)
            return q
              .eq('is_deleted', false)
              .eq('is_published', args.is_published);

          return q.eq('is_deleted', false);
        })
        .paginate(args.paginationOpts);

results = await ctx.db
        .query('skills')
        .withIndex('search_fields', (q) => {
          if (
            args.is_published !== undefined &&
            args.is_featured !== undefined
          )
            return q
              .eq('is_deleted', false)
              .eq('is_published', args.is_published)
              .eq('is_featured', args.is_featured);

          if (args.is_featured !== undefined)
            return q
              .eq('is_deleted', false)
// ERROR : Argument of type '"is_featured"' is not assignable to parameter of type '"is_published"'.
              .eq('is_featured', args.is_featured);

          if (args.is_published !== undefined)
            return q
              .eq('is_deleted', false)
              .eq('is_published', args.is_published);

          return q.eq('is_deleted', false);
        })
        .paginate(args.paginationOpts);

typescript force me to apply filter in order i have defined them in schema

.index('search_fields', [
      'is_deleted',
      'is_published',
      'is_featured',
    ])

.index('search_fields', [
      'is_deleted',
      'is_published',
      'is_featured',
    ])

lee•4mo ago

yep that's how indexes work (https://stack.convex.dev/databases-are-spreadsheets ). If you want to do a different order, you'll need a second index. Good idea to do the filtering in an index btw

Databases are Spreadsheets

I want to share my mental model of databases: - Databases are just big spreadsheets - An index is just a view of the spreadsheet sorted by one or mor...

M ZeeshanOP•4mo ago

I followed the link you provided and noticed the author used two indices, resulting in two separate database queries. However, no example code was given to handle the combined results. I assume I need to merge the results manually. As a temporary solution, I added @ts-ignore to bypass the TypeScript error, but I'm looking for a more robust solution to properly handle it.

if (args.is_featured !== undefined)
  return (
    q
      .eq('is_deleted', false)
      // @ts-ignore
      .eq('is_featured', args.is_featured)
  );

if (args.is_featured !== undefined)
  return (
    q
      .eq('is_deleted', false)
      // @ts-ignore
      .eq('is_featured', args.is_featured)
  );

lee•4mo ago

I don't think you can ignore the error, it will throw at runtime. You should be able to have separate return values without merging like

if (args.is_featured !== undefined) {
  return ctx.db.query("tbl").withIndex("index1", ...).paginate(opts);
} else {
  return ctx.db.query("tbl").withIndex("index2", ...).paginate(opts);
}

if (args.is_featured !== undefined) {
  return ctx.db.query("tbl").withIndex("index1", ...).paginate(opts);
} else {
  return ctx.db.query("tbl").withIndex("index2", ...).paginate(opts);
}

M ZeeshanOP•4mo ago

sure... but it works without runtime error

lee•4mo ago

does it really? that would be a convex bug, since it's definitely not filtering by "is_featured", it's filtering by the second field in the index (which is "is_published")

M ZeeshanOP•4mo ago

well after 10 quries i got error Uncaught Error: Tried to query index skills.search_fields but the query didn't use the index fields in order. Index fields: ["is_deleted", "is_published", "is_featured", "_creationTime"] Query fields: ["is_deleted", "is_featured"] First incorrect field: "is_featured" i need to use separate return values

lee•4mo ago

cool that's the error i was expecting. Weird it didn't happen immediately

M ZeeshanOP•4mo ago

Yep! Thanks for taking the time to help me out. I really appreciate your assistance!

lee•4mo ago

we should definitely have more examples. Nice job figuring it out 😊

M ZeeshanOP•4mo ago

is there a way to add offset-based pagination...?

lee•4mo ago

well you can do most things you could want with this https://stack.convex.dev/pagination but there are definitely not enough examples there either

Take Control of Pagination

Convex offers robust control over pagination with a powerful function, getPage, enabling complex edge cases. In this article, we go over how to use th...

M ZeeshanOP•4mo ago

i will experiment and ask for help

How to apply filter before pagination?

Did you find this page helpful?