wrux•2w ago

Query, filtering array of IDs

I have a jobs schema which has a skills property which is just an array of ID references.

jobs: defineTable({
  title: v.string(),
  clientId: v.id('users'),
  skills: v.optional(v.array(v.id('skills'))),
}).index('by_clientId', ['clientId'])

jobs: defineTable({
  title: v.string(),
  clientId: v.id('users'),
  skills: v.optional(v.array(v.id('skills'))),
}).index('by_clientId', ['clientId'])

In my application, I want to filter by skills. What is the best way to query a set union on skills and my args. I know the following query won't work. I'm just wondering if something like this is possible:

let jobsQuery = ctx.db
  .query('jobs')
  .filter((q) => q.eq(q.field('status'), args.status))
  .filter((q) => {
    q.and(q.field('skills'), q.contains(q.field('skills'), args.skills))
  });

let jobsQuery = ctx.db
  .query('jobs')
  .filter((q) => q.eq(q.field('status'), args.status))
  .filter((q) => {
    q.and(q.field('skills'), q.contains(q.field('skills'), args.skills))
  });

26 Replies

Convex Bot•2w ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

lee•2w ago

Check out https://stack.convex.dev/complex-filters-in-convex

Using TypeScript to Write Complex Query Filters

There’s a new Convex helper to perform generic TypeScript filters, with the same performance as built-in Convex filters, and unlimited potential.

wruxOP•2w ago

@Lee So is the recommended solution to over-fetch and then remove the unwanted values?

lee•2w ago

Either that or use an index on a table that maps from skill -> job

wruxOP•2w ago

What is the reccomended route though?

lee•2w ago

Personally i would do the index route, but that might be premature optimization, depending on how many documents you have

wruxOP•2w ago

Interesting, ok

lee•2w ago

It's all a matter of tradeoffs. Efficiency vs ease of implementation

wruxOP•2w ago

Is filtering array values in the product roadmap? Seems like it would be a great addition

lee•2w ago

There are two supported ways to filter an array, both described in that article. If you have a suggestion for a better way, we'd be interested to hear it The "overfetch and filter out" method is pretty close to what you would get in a sql or mongo db, for example

wruxOP•2w ago

There's a example of code that would be nice if it's possible in that article:

export const postsWithTag = query({
  args: { tag: v.string() },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("posts")
      // Doesn't work because q.arrayIncludes doesn't exist.
      .filter((q) => q.arrayIncludes(q.field("tags"), args.tag)
      .collect();
  },
});

export const postsWithTag = query({
  args: { tag: v.string() },
  handler: async (ctx, args) => {
    return await ctx.db
      .query("posts")
      // Doesn't work because q.arrayIncludes doesn't exist.
      .filter((q) => q.arrayIncludes(q.field("tags"), args.tag)
      .collect();
  },
});

.arrayIncludes() would be nice

lee•2w ago

You can do that with the filter function, calling doc.tags.includes in a typescript predicate It would do the same thing under the hood, and it's not much more code

wruxOP•2w ago

But let's say the posts object has a lot of data, then isn't it going to send down a all of that data for in the records we don't need? That's going to be using lots of DB bandwidth quota

lee•2w ago

DB bandwidth includes documents filtered out by the .filter method too The only way to exclude a document from counting towards bandwidth is to skip it entirely with an index

wruxOP•2w ago

Is bandwidth considered data going between convex functions and the DB backend? Or is it between the convex function and the client consuming the API?

lee•2w ago

Bandwidth is between convex function and the underlying db Which includes .filter because in order to be filtered out it needs to be read from the db

wruxOP•2w ago

Ok thanks So is there no plan at all to add functions that allow filtering before .collect()? Seems like it should be a core feature

lee•2w ago

That's what indexes are for

wruxOP•2w ago

Can you give an example of a schema and query?

lee•2w ago

https://stack.convex.dev/complex-filters-in-convex#array-includes-with-index-and-a-join

Using TypeScript to Write Complex Query Filters

There’s a new Convex helper to perform generic TypeScript filters, with the same performance as built-in Convex filters, and unlimited potential.

lee•2w ago

(that's a link to a section that has schema and query) Btw i noticed the docs don't make it clear that .filtered documents still count as bandwidth, so thanks for pointing that out

wruxOP•2w ago

Ok that's concerning. I'm working on an app and before asking this question I was filtering my data like the article recommends, by filtering after .collect() . I'm seeing fairly large DB bandwidth usage in local development without much data in the database. I think maybe Convex isn't aimed at my usecase

lee•2w ago

If you're concerned about bandwidth, it sounds like you want to use more indexes. If you describe your query in detail we can help you write indexed queries to solve it

wruxOP•2w ago

Ok so here's an example schema:

const schema = defineSchema({
  ...authTables,
  skills: defineTable({
    title: v.string(),
  }),
  jobs: defineTable({
    title: v.string(),
    clientId: v.id('users'),
    skills: v.optional(v.array(v.id('skills'))),
  }).index('by_clientId', ['clientId']),
});

const schema = defineSchema({
  ...authTables,
  skills: defineTable({
    title: v.string(),
  }),
  jobs: defineTable({
    title: v.string(),
    clientId: v.id('users'),
    skills: v.optional(v.array(v.id('skills'))),
  }).index('by_clientId', ['clientId']),
});

Then on the frontend I'm listing all jobs and on the sidebar showing filters for skills. Let's say I select Designer and Developer, the query should filter jobs with those skills inside the skills key on the jobs object. The suggested query to fetch everything and then filter in JS isn't going to work if we have 100,000 jobs in the DB

lee•2w ago

cool. to be clear, there are two suggestions: one using filter in js, and the other using indexes. if you're worried about bandwidth or having 100k jobs, then you should use indexes. does this pattern work for you? Specifically i'm suggesting this:

lee•2w ago

message.txt

Query, filtering array of IDs

Did you find this page helpful?