Riki•12mo ago

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,
    };
  },
});

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 🙏

5 Replies

erquhart•12mo ago

You want to use .take(5) to limit the join table query, but I don’t believe there’s a way to do that with getManyFrom(), or an alternative with convex helpers. @ian might correct me on that one! But I believe you’ll want to replace getManyFrom() with a regular indexed query using .take(). The rest of your code should still work, although I’m wondering if link.groupId should be link.eventId.

RikiOP•12mo ago

Thanks for help @erquhart . Sorry for the typo, indeed link.groupId => link.eventId. I have just fixed it in my first post. If I understand correctly, given the date attribute is on the event entity, using take(n) on the query of the join table won't allow me to retrieve the first 5 events by date? Or I am misunderstanding something

erquhart•12mo ago

Oh duh 🤦‍♂️ absolutely right. Is the user to event relationship one to many or many to many? Nvm I see you answered that in your post Not knowing the nature of your app, if the likelihood of a user having more than a few hundred events is low, your current approach may be just fine. You can filter the list down to the next 5 on the server so you’re not returning more data than necessary to the client. If performance or data consumption turn out to be problematic, you may be able to denormalize a bit to enable the kind of query you want. For example, you could add the event date to the join table, and then use the approach I mentioned initially. You would then need to keep those fields synced with the event data, but that’s pretty straightforward to do. Depending on how your app works, the syncing effort could offset the optimization, so you’ll want to consider what’s ideal for your usage.

ian•12mo ago

Agreed- I would store the event date on the join table and add it to that index, or if a user will only have hundreds of events or less, just grab them all and filter on the server side

RikiOP•12mo ago

Alright that's clear Thanks for your help. Lovely community!

Optimizing query with joins and criteria on the last table

Did you find this page helpful?