JordanJ
Convex Community•12mo ago•
14 replies
Jordan

filter index based on multiple possible values?

consider the following query, where we want to select all posts made by a user, with a particular status of pending. due to lots of users, and lots of posts per user, we have an index set up to index posts by ["userId", "status"]. therefore, we query like so:

    const userPosts = await ctx.db
      .query("posts")
      .withIndex("by_userId_status", (q) => q.eq("userId", user?._id).eq('status', 'pending')
      .collect();


now consider we want to allow querying based off multiple status values. lets, select all posts made by a given user that are pending or in_progress. we would have to write the following:

    const userPosts = await ctx.db
      .query("posts")
      .withIndex("by_userId_status", (q) => q.eq("userId", user?._id).eq('status', 'pending').eq('status', 'in_progress))
      .collect();


but what if we wanted to make list of statuses an argument, that could be passed from the FE (i.e. a user filters their own posts down to any combination of pending, in_progress completed or cancelled


in order to do this, we currently have to use the convex-helper filter or a basic TS array-filter after we query the results from the index. but cannot actively filter out within withIndex , which defeats the purpose of a by_userId_status index. it would look something like this:

    const userPosts = await ctx.db
      .query("posts")
      .withIndex("by_userId_status", (q) => q.eq("userId", user?._id)
      .collect();

      // where args.status is of type: v.array(v.string())
      return userPosts.filter(({status})=>args.status.includes(status)))


how might we achieve the desired performance with the approach of using arguments to index off of?
Was this page helpful?