How to implement pagination with join and filtering

Given that I have these 2 tables that describes a 1-to-many relationship: "a user can be member of 1 or many groups"

defineSchema({
   groups: defineTable({
      group_name: v.string(),
   }),
   members: defineTable({
      group_id: v.id("groups),
      user_id: v.id("users") 
   })
})

defineSchema({
   groups: defineTable({
      group_name: v.string(),
   }),
   members: defineTable({
      group_id: v.id("groups),
      user_id: v.id("users") 
   })
})

What would be the convex way to implement the following paginated query: get groups of user A (each page has 10 items). This is (a naive) equivalent SQL query:

SELECT G.* FROM groups G
JOIN members M ON G._id = M.group_id
WHERE M.user_id = 'A'
LIMIT 10 OFFSET __page_offset__

SELECT G.* FROM groups G
JOIN members M ON G._id = M.group_id
WHERE M.user_id = 'A'
LIMIT 10 OFFSET __page_offset__

3 Replies

Convex Bot•4w 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•4w ago

For this you need to: 1. Get the members with a given user id / group ID 2. Grab the associated group info So if you'd want to fetch the members by user_id you'd want an index:

defineSchema({
   groups: defineTable({
      group_name: v.string(),
   }),
   members: defineTable({
      group_id: v.id("groups),
      user_id: v.id("users") 
   }).index("by_user_id", ["user_id"])
})

defineSchema({
   groups: defineTable({
      group_name: v.string(),
   }),
   members: defineTable({
      group_id: v.id("groups),
      user_id: v.id("users") 
   }).index("by_user_id", ["user_id"])
})

And you can then paginate the members by it:

const members = await ctx.db.query("members")
  .withIndex("by_user_id", q => q.eq("user_id", someUserId))
  .paginate({ numItems: 10, cursor });

const members = await ctx.db.query("members")
  .withIndex("by_user_id", q => q.eq("user_id", someUserId))
  .paginate({ numItems: 10, cursor });

And join in the group data (here just adding the member id, but could also add other user/ member data):

const pageWithGroups = await Promise.all(members.page, async (member) => {
  const group = await ctx.db.get(member.group_id);
  return { member_id: member._id, ...group };
});

const pageWithGroups = await Promise.all(members.page, async (member) => {
  const group = await ctx.db.get(member.group_id);
  return { member_id: member._id, ...group };
});

Then return the paginated data:

return {
  ...members,  
  page: pageWithGroups,
};

return {
  ...members,  
  page: pageWithGroups,
};

noob saibotOP•4w ago

THanks @ian for this answer

How to implement pagination with join and filtering

Did you find this page helpful?