noob saibot•2w ago

Help me better understand paginated queries

I'm having trouble running a paginated query in server using the filter from the convex-helpers library when, in client, I give a small value to initialNumItems. I have 2 tables: table campaigns

 _id,
 campaign_name

 _id,
 campaign_name

table members

_id,
campaign_id,
user_id

_id,
campaign_id,
user_id

Now I want to find all the campaigns where "user_id" is part of, using a paginated query. So I write this function:

args: {
 user_id: v.id("members),
 paginationOpts: paginationOptsValidator,
},
handler: async (ctx, { user_id, paginationOpts }) => {
  // first I find all records from table "members" for user "user_id"
  const members = await ctx.db.query("members").withIndex("by_user_id", ...);

  // then paginated query of campaigns using the 'filter' method from `convex-helpers`
  const result = await filter(ctx.db.query("campaigns"), (cp) => members.some((mem) => mem.campaign_id === cp._id)).paginate(paginationOpts)
...
}

args: {
 user_id: v.id("members),
 paginationOpts: paginationOptsValidator,
},
handler: async (ctx, { user_id, paginationOpts }) => {
  // first I find all records from table "members" for user "user_id"
  const members = await ctx.db.query("members").withIndex("by_user_id", ...);

  // then paginated query of campaigns using the 'filter' method from `convex-helpers`
  const result = await filter(ctx.db.query("campaigns"), (cp) => members.some((mem) => mem.campaign_id === cp._id)).paginate(paginationOpts)
...
}

The last query will return data only if I give a bigger value to initialNumItems: with value 3 it returns 2 records, but with 10 then it find the 3 records I'm expecting. It seems to me that the filtering I'm applying is messing up the cursor and perhaps the first record found by the pagination is beyond the initial cursor?

6 Replies

Convex Bot•2w ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

ian•2w ago

To most efficiently fetch this data, paginate over the members table, then do a db.get for all the associated campaigns. As is, it's walking the entire campaigns table and applying the filter to each row, so it might be getting exhausted in how many rows it can fetch before it just returns what it's found so far.

ian•2w ago

ah yeah, the code for paginate in the filter helper is just applying the filter to the paginated results: https://github.com/get-convex/convex-helpers/blob/main/packages/convex-helpers/server/filter.ts#L55

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

noob saibotOP•2w ago

@ian Thanks for the reply. I refactored my code to paginate over the members table. It is working as expected. But the fact that paginate applies the filter after collecting the data, doesn't this lead to errors? For example in my case, given the user_id, these are the 4 data in the members table:

(mem_1, camp_1),
(mem_2, camp_2),
(mem_3, camp_3),
(mem_4, undefined)

(mem_1, camp_1),
(mem_2, camp_2),
(mem_3, camp_3),
(mem_4, undefined)

Now with initialNumItems = 3 and the below (simplified) query:

// paginate members:
const filters = await filter(ctx.db.query("members"), async (mem) => {
  console.log("mem") <-- I'm logging this record and I see that mem_1 is never found
  ...
})

// paginate members:
const filters = await filter(ctx.db.query("members"), async (mem) => {
  console.log("mem") <-- I'm logging this record and I see that mem_1 is never found
  ...
})

If I run the above query without adding .order("descr"), I'm observing that the records mem_4, mem_3, mem_2 are selected. The consequence is the query returns only 2 records (because mem_4 is not associated with a campaign). But if I apply .order("descr") then I get the 3 expected records: so the order of pagination matters. I would expect convex to, first, find all records of the filter and then apply pagination (that's how to preview system I used - Hasura + Postgres - worked, unless I'm mistaken). The next observation: I decided to not user the helper function filter and reverted to the built-in ctx.db.query. And it seems to work as expected even without applying .order("descr"). The below query does return 3 records even without order:

    const paginated_members = await ctx.db
      .query("members")
      .withIndex("by_user", (q) => q.eq("user_id", ctx.user._id))
      .filter((q) => q.neq(q.field("campaign_id"), undefined))
      .paginate(args.paginationOpts);

    const paginated_members = await ctx.db
      .query("members")
      .withIndex("by_user", (q) => q.eq("user_id", ctx.user._id))
      .filter((q) => q.neq(q.field("campaign_id"), undefined))
      .paginate(args.paginationOpts);

ian•2w ago

not sure if this is a typo, but .order("descr") isn't a thing I know of. .order("desc") is the way to reverse the ordering. I'm not sure why you're seeing different results with different orders, maybe to do with the typo, but ordering shouldn't affect anything. If you have a repro of that it'd be great to get to the bottom of. Glad the pagination is working for you!

noob saibotOP•2w ago

yes, that's just a typo, in my code I don't have a typo.

Help me better understand paginated queries

Did you find this page helpful?