dannyelo•3mo ago

Search Index by Two or More Fields

Hello, I'm trying to retrive a query using searchIndex. I need it to query documents by two or more fields. This is what I tried so far, but is only retriving results for legalName.

customers: defineTable({
  organizationId: v.id('organizations'),
  alias: v.string(),
  legalName: v.optional(v.string()),
  // ...rest of the fields...
})
  .index('by_organizationId', ['organizationId'])
  .searchIndex('search_legal_name', {
    searchField: 'legalName',
    filterFields: ['organizationId'],
  })
  .searchIndex('search_alias', {
    searchField: 'alias',
    filterFields: ['organizationId'],
  }),

customers: defineTable({
  organizationId: v.id('organizations'),
  alias: v.string(),
  legalName: v.optional(v.string()),
  // ...rest of the fields...
})
  .index('by_organizationId', ['organizationId'])
  .searchIndex('search_legal_name', {
    searchField: 'legalName',
    filterFields: ['organizationId'],
  })
  .searchIndex('search_alias', {
    searchField: 'alias',
    filterFields: ['organizationId'],
  }),

export const getCustomersSearch = query({
  args: {
    search: v.string(),
  },
  handler: async (ctx, args) => {
    const currentOrganization = await getCurrentOrganization(ctx)
    let customers: Doc<'customers'>[] = []

    const byLegalName = await ctx.db
      .query('customers')
      .withSearchIndex('search_legal_name', (q) =>
        q
          .search('legalName', args.search)
          .eq('organizationId', currentOrganization._id),
      )
      .take(10)

    const byAlias = await ctx.db
      .query('customers')
      .withSearchIndex('search_alias', (q) =>
        q
          .search('alias', args.search)
          .eq('organizationId', currentOrganization._id),
      )
      .take(10)

    customers = [...byLegalName, ...byAlias]

    return await Promise.all(
      customers.map(async (customer) => transformCustomer(customer._id, ctx)),
    )
  },
})

export const getCustomersSearch = query({
  args: {
    search: v.string(),
  },
  handler: async (ctx, args) => {
    const currentOrganization = await getCurrentOrganization(ctx)
    let customers: Doc<'customers'>[] = []

    const byLegalName = await ctx.db
      .query('customers')
      .withSearchIndex('search_legal_name', (q) =>
        q
          .search('legalName', args.search)
          .eq('organizationId', currentOrganization._id),
      )
      .take(10)

    const byAlias = await ctx.db
      .query('customers')
      .withSearchIndex('search_alias', (q) =>
        q
          .search('alias', args.search)
          .eq('organizationId', currentOrganization._id),
      )
      .take(10)

    customers = [...byLegalName, ...byAlias]

    return await Promise.all(
      customers.map(async (customer) => transformCustomer(customer._id, ctx)),
    )
  },
})

9 Replies

Convex Bot•3mo 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!

erquhart•3mo ago

The best we have for now is to create an additional field to query against - in your case that field would combine legal name and alias into a single string, and search against that. You would keep the field up to date by updating it every time either of the other two fields are updated. Best way to do this is to have a helper function that's solely responsible for any updates to the record (or at least to these two fields), so you can be sure the computed field is always accurate.

dannyeloOP•3mo ago

I understand, so will have to concatenate to a big string. So I can only define one searchIndex per table?

erquhart•3mo ago

No you can have multiple, but I believe only one per field

dannyeloOP•3mo ago

I have one per field

erquhart•3mo ago

https://docs.convex.dev/search/text-search#limits

dannyeloOP•3mo ago

I can't use 2 .withSearchIndex in the same query function?

erquhart•3mo ago

No, only one No special limits it seems, I thought there were

So I can only define one searchIndex per table?

You can define multiple per table, but still can only use one per query

dannyeloOP•3mo ago

Sorry, I think my query is working fine I think I have an error in my front end code Let me see.. I remove the duplicates like this...

export const getCustomersSearch = query({
  args: {
    search: v.string(),
  },
  handler: async (ctx, args) => {
    const currentOrganization = await getCurrentOrganization(ctx)
    let customers: Doc<'customers'>[] = []
    const byLegalName = await ctx.db
      .query('customers')
      .withSearchIndex('search_legal_name', (q) =>
        q
          .search('legalName', args.search)
          .eq('organizationId', currentOrganization._id),
      )
      .take(10)

    const byAlias = await ctx.db
      .query('customers')
      .withSearchIndex('search_alias', (q) =>
        q
          .search('alias', args.search)
          .eq('organizationId', currentOrganization._id),
      )
      .take(10)

    const uniqueCustomers = new Map(
      [...byLegalName, ...byAlias].map((customer) => [customer._id, customer]),
    )
    customers = Array.from(uniqueCustomers.values())

    return await Promise.all(
      customers.map(async (customer) => transformCustomer(customer._id, ctx)),
    )
  },
})

export const getCustomersSearch = query({
  args: {
    search: v.string(),
  },
  handler: async (ctx, args) => {
    const currentOrganization = await getCurrentOrganization(ctx)
    let customers: Doc<'customers'>[] = []
    const byLegalName = await ctx.db
      .query('customers')
      .withSearchIndex('search_legal_name', (q) =>
        q
          .search('legalName', args.search)
          .eq('organizationId', currentOrganization._id),
      )
      .take(10)

    const byAlias = await ctx.db
      .query('customers')
      .withSearchIndex('search_alias', (q) =>
        q
          .search('alias', args.search)
          .eq('organizationId', currentOrganization._id),
      )
      .take(10)

    const uniqueCustomers = new Map(
      [...byLegalName, ...byAlias].map((customer) => [customer._id, customer]),
    )
    customers = Array.from(uniqueCustomers.values())

    return await Promise.all(
      customers.map(async (customer) => transformCustomer(customer._id, ctx)),
    )
  },
})

The results are logging in the console, I must have a bug that is not getting into my combobox Can't find it yet

Search Index by Two or More Fields

Did you find this page helpful?