unsphere•15mo ago

Multiple queries suggestion

I have a queue list and thinking about the best query design, performance and resources wise. The queue displays tickets as follows: 1. active (one) 2. prioritized (multiple) creation time asc 3. in queue (multiple) creation time asc 4. processed (multiple) processed time desc I am also thinking about pagination for prioritized, in queue and processed ones so that the user sees the active one + 20 after that in the order specified on top and on scroll i load 20 more either prioritized ones if not end reached or in queue ones if not end reached and so on. Should I create 4 queries with indexes including pagination leveraging skip feature if end is reached of the previous query or should I put all in one like:

export const list = query({
  args: { receiverId: v.id('users') },
  handler: async (ctx, { receiverId }) => {
    const [active, prioritized, queue, processed] = await Promise.all([
      ctx.db
        .query('tickets')
        .withIndex('byReceiverIdAndIsActive', (q) =>
          q.eq('receiverId', receiverId).eq('isActive', true)
        )
        .first(),
      ctx.db
        .query('tickets')
        .withIndex('byReceiverIdAndIsActiveAndIsPrioritizedAndIsProcessed', (q) =>
          q
            .eq('receiverId', receiverId)
            .eq('isActive', false)
            .eq('isPrioritized', true)
            .eq('isProcessed', false)
        )
        .order('asc')
        .collect(),
      ctx.db
        .query('tickets')
        .withIndex('byReceiverIdAndIsActiveAndIsPrioritizedAndIsProcessed', (q) =>
          q
            .eq('receiverId', receiverId)
            .eq('isActive', false)
            .eq('isPrioritized', false)
            .eq('isProcessed', false)
        )
        .order('asc')
        .collect(),
      ctx.db
        .query('tickets')
        .withIndex('byReceiverIdAndIsProcessed', (q) =>
          q.eq('receiverId', receiverId).eq('isProcessed', true)
        )
        .order('desc')
        .collect(),
    ]);
...

export const list = query({
  args: { receiverId: v.id('users') },
  handler: async (ctx, { receiverId }) => {
    const [active, prioritized, queue, processed] = await Promise.all([
      ctx.db
        .query('tickets')
        .withIndex('byReceiverIdAndIsActive', (q) =>
          q.eq('receiverId', receiverId).eq('isActive', true)
        )
        .first(),
      ctx.db
        .query('tickets')
        .withIndex('byReceiverIdAndIsActiveAndIsPrioritizedAndIsProcessed', (q) =>
          q
            .eq('receiverId', receiverId)
            .eq('isActive', false)
            .eq('isPrioritized', true)
            .eq('isProcessed', false)
        )
        .order('asc')
        .collect(),
      ctx.db
        .query('tickets')
        .withIndex('byReceiverIdAndIsActiveAndIsPrioritizedAndIsProcessed', (q) =>
          q
            .eq('receiverId', receiverId)
            .eq('isActive', false)
            .eq('isPrioritized', false)
            .eq('isProcessed', false)
        )
        .order('asc')
        .collect(),
      ctx.db
        .query('tickets')
        .withIndex('byReceiverIdAndIsProcessed', (q) =>
          q.eq('receiverId', receiverId).eq('isProcessed', true)
        )
        .order('desc')
        .collect(),
    ]);
...

11 Replies

jamwt•15mo ago

@unsphere hey, would you be up for posting your schema.ts just so we can see everything together?

unsphereOP•15mo ago

@jamwt sure there you go:

export default defineSchema({
  users: defineTable({
    clerkId: v.string(),
    username: v.string(),
    imageUrl: v.optional(v.string()),
  })
    .index('byClerkId', ['clerkId'])
    .index('byUsername', ['username']),
  tickets: defineTable({
    authorId: v.id('users'),
    receiverId: v.id('users'),
    message: v.optional(v.string()),
    isPrioritized: v.boolean(),
    isActive: v.boolean(),
    isProcessed: v.boolean(),
    startDate: v.optional(v.number()),
    endDate: v.optional(v.number()),
  })
    .index('byAuthorId', ['authorId'])
    .index('byReceiverId', ['receiverId'])
    .index('byReceiverIdAndIsActive', ['receiverId', 'isActive'])
    .index('byReceiverIdAndIsPrioritized', ['receiverId', 'isPrioritized'])
    .index('byReceiverIdAndIsProcessed', ['receiverId', 'isProcessed'])
    .index('byReceiverIdAndIsActiveAndIsPrioritizedAndIsProcessed', [
      'receiverId',
      'isActive',
      'isPrioritized',
      'isProcessed',
    ]),
  follows: defineTable({
    followerId: v.id('users'),
    ticketId: v.id('tickets'),
  })
    .index('byFollowerId', ['followerId'])
    .index('byTicketId', ['ticketId'])
    .index('byFollowerIdAndTicketId', ['followerId', 'ticketId']),
});

export default defineSchema({
  users: defineTable({
    clerkId: v.string(),
    username: v.string(),
    imageUrl: v.optional(v.string()),
  })
    .index('byClerkId', ['clerkId'])
    .index('byUsername', ['username']),
  tickets: defineTable({
    authorId: v.id('users'),
    receiverId: v.id('users'),
    message: v.optional(v.string()),
    isPrioritized: v.boolean(),
    isActive: v.boolean(),
    isProcessed: v.boolean(),
    startDate: v.optional(v.number()),
    endDate: v.optional(v.number()),
  })
    .index('byAuthorId', ['authorId'])
    .index('byReceiverId', ['receiverId'])
    .index('byReceiverIdAndIsActive', ['receiverId', 'isActive'])
    .index('byReceiverIdAndIsPrioritized', ['receiverId', 'isPrioritized'])
    .index('byReceiverIdAndIsProcessed', ['receiverId', 'isProcessed'])
    .index('byReceiverIdAndIsActiveAndIsPrioritizedAndIsProcessed', [
      'receiverId',
      'isActive',
      'isPrioritized',
      'isProcessed',
    ]),
  follows: defineTable({
    followerId: v.id('users'),
    ticketId: v.id('tickets'),
  })
    .index('byFollowerId', ['followerId'])
    .index('byTicketId', ['ticketId'])
    .index('byFollowerIdAndTicketId', ['followerId', 'ticketId']),
});

And I forgot to mention that I also need to include the author for each ticket to show username and image and how much followers each ticket has.

CodingWithJamal•15mo ago

um i would recommend not indexing so many columns on a single table. Because it could make your db querys slower

lee•15mo ago

having several indexes should be fine -- i wouldn't remove them if you're using them unless you're seeing performance issues for the original question, i think having several paginated queries makes sense, with skip or conditionally rendered components it gets complicated to paginate on multiple things in the same query

CodingWithJamal•15mo ago

but doesn’t it cost more work every time you insert on an index because it has to sort the row into the index?

lee•15mo ago

yes that's true, but you should need a significant number of indexes before you start to notice the overhead. in most cases the cost of a single additional index will be offset by the benefit of using the index in a query that would otherwise have to scan more data.

CodingWithJamal•15mo ago

okay i see

unsphereOP•15mo ago

yes okay then I will wait for the skip feature for pagination. As far I can see there is no possibility to have a paginate query with joins to user and follows or? because .paginate(args.paginationOpts) needs to be returned from the query. So I need to query the user and follows inside the ticket view.

lee•15mo ago

You can do joins as long as the query only does one pagination.

const result = await ctx.db.query("tickets").withIndex(...).paginate(opts);
const page = await Promise.all(result.page.map(async (ticket) => {
  const author = await ctx.db.get(ticket.author);
  return {...ticket, author};
}));
return {...result, page};

const result = await ctx.db.query("tickets").withIndex(...).paginate(opts);
const page = await Promise.all(result.page.map(async (ticket) => {
  const author = await ctx.db.get(ticket.author);
  return {...ticket, author};
}));
return {...result, page};

We should add an example like this to the documentation. Sorry if there are typos, i'm on my phone.

CodingWithJamal•15mo ago

so under the hood convex handles the primary keys / joins for us? How do we know when this happens

lee•15mo ago

it's not under the hood. In the code snippet i'm doing an explicit join. There are more examples in https://stack.convex.dev/functional-relationships-helpers

Functional Relationships: Helpers

In this post, we’ll look at some helper functions to help write code to traverse relationships in a readable, predictable, and debuggable way.

Multiple queries suggestion

Did you find this page helpful?