PierreP
Convex Community•2y ago•
9 replies
Pierre

Optimizing query with joins and criteria on the last table

Hello, I wanted to know if there is a way/pattern to optimize the following query:

Context: I have an entity 
users
 , events, and the many-to-many join table usersEvents.
Goal: Given a user, I want to see its upcoming next 5 events (date sorted).

What I have done so far:

export const getUserProfile = query({
  args: {userId: v.id("users")},
  handler: async (ctx, args) => {
    const user = await ctx.db.get(args.userId)

    const events = await asyncMap(
      await getManyFrom(
        ctx.db,
        'usersEvents',
        'userId',
        args.userId,
      ),
      link =>
        ctx.db
          .query('events')
          .withIndex('by_id_date', q =>
            q.eq('id', link.eventId).gt('date', Date.now()),
          )
          .unique(),
    );
    return {
      user: user,
// I'll sort by date and take the 5 first items on client side
      events: events,
    };
  },
});


Problem:
This is not optimized given I retrieve/scan all the future events of my user => Bandwidth consumption increase. I just would like to take the 5 next ones happening in the future.

If someone could help me/give me some light to optimize this query. Thanks in advance 🙏
Was this page helpful?