14 replies

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(),
    ]);
...

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(),
    ]);
...

Multiple queries suggestion

Similar Threads

Similar Threads

Similar Threads