JordanJ
Convex Community•14mo 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?