kamal.panara•2w ago

advance filtering

i just realized that convex doesn't support advanced filtering like checking if array includes given items. is there any work around for now ? i tried to filter in ts but i'm using pagination as well & search index if user is searching, else normal index.

15 Replies

Hmza•2w ago

you can do an alternative to includes by doing something like this: results = results.filter((q) => q.or( q.eq(q.field("assignedField"), userId), q.or(...YOURARRAY.map(id => q.eq(q.field("_id"), id))) ) ); assuming you have that ARRAY calculated already with items to find in convex table.

kamal.panaraOP•2w ago

My schema looks like this.

contacts: defineTable({
     ...other fields...
     tags: v.optional(v.array(v.id("tags"))),
     searchFields: v.optional(v.string()),
  }),


tags: defineTable({
      ...other fields...
      name: v.string(),
      status: v.union(v.literal("active"), v.literal("deleted")),
   }),

contacts: defineTable({
     ...other fields...
     tags: v.optional(v.array(v.id("tags"))),
     searchFields: v.optional(v.string()),
  }),


tags: defineTable({
      ...other fields...
      name: v.string(),
      status: v.union(v.literal("active"), v.literal("deleted")),
   }),

My existing query looks like this:

 const results = ((args.searchKeyword || "").trim().length > 0)
    ? await ctx.db
          .query("contacts")
          .withSearchIndex("search_fields", (q) =>
            q.search('searchFields', args.searchKeyword!)
            .eq("organizationId", orgId._id)
            .eq("status", "active"),
          )
            .paginate(args.paginationOpts)
    : await ctx.db
          .query("contacts")
          .withIndex("by_org_id_status", (q) =>
            q.eq("organizationId", orgId._id)
            .eq("status", "active"),
          )
            .paginate(args.paginationOpts);
// issue: this method leads to filter only contacts in current page.
if (args.filterByTags && args.filterByTags.length > 0) {
    results.page = results.page
      .filter((contact) => 
          contact.tags?.some(tag => args.filterByTags?.includes(tag))
      );
}

 const results = ((args.searchKeyword || "").trim().length > 0)
    ? await ctx.db
          .query("contacts")
          .withSearchIndex("search_fields", (q) =>
            q.search('searchFields', args.searchKeyword!)
            .eq("organizationId", orgId._id)
            .eq("status", "active"),
          )
            .paginate(args.paginationOpts)
    : await ctx.db
          .query("contacts")
          .withIndex("by_org_id_status", (q) =>
            q.eq("organizationId", orgId._id)
            .eq("status", "active"),
          )
            .paginate(args.paginationOpts);
// issue: this method leads to filter only contacts in current page.
if (args.filterByTags && args.filterByTags.length > 0) {
    results.page = results.page
      .filter((contact) => 
          contact.tags?.some(tag => args.filterByTags?.includes(tag))
      );
}

I want to filter contacts which includes tags that user is filtering with pagination & search. can you please explain a bit on how to implement this ?

Hmza•2w ago

i see, the paginate should be applied at the very end, when you're done filtering data. you should move the filter((contact) => before the .paginate

kamal.panaraOP•2w ago

that filter is ts filter, not convex db filter, convex doesn't support .include queries, does it ? i now kinda understand your logic, but i think it will only find exact match ? or it will work like my ts filter .some includes query ?

Hmza•2w ago

you're right. yeah so you can use the convex filter instead of ts filter to find relevant tags, instead of doing .some(tag => args.filterByTags?.includes(tag) you can do q.or and q.and to match the tags. but i'm now seeing that tags is not scalar. can you provide the complete schema of both tables and this function in full ? only way i can test at fullest.

kamal.panaraOP•2w ago

message.txt

kamal.panaraOP•2w ago

you can ignore user auth, org stuff from query.

kamal.panaraOP•2w ago

message.txt

kamal.panaraOP•2w ago

thats my schema. as i said you can remove users & org tables & related fields from contacts & tags table.

Hmza•2w ago

so i just tried this results.page = results.page.filter((contact) => Array.isArray(contact.tags) && contact.tags.some(tag => args.filterByTags!.includes(tag)) ); and it works for me. can you check on your end?

kamal.panaraOP•2w ago

i konw that it works, main problem with this logic is it only works if all your matching documents are in first page. how many docs are you loading per page ? try addding more docuemnts i have 80 docuemnts for testing, and i load 10 docs per page, so filter applies to 10 docs only. then when i hit load more i see more matching docs but it's not good UX because if there are only 3 matching results in first page, user thinks that, that's all matching docs because per page it should load 10 docs. i want to load 10 docs that matching from all docs.

Hmza•2w ago

i understand so its only going to load the documents that has those filtered tags in them right. despite you using more with loadMore.

kamal.panaraOP•2w ago

and scaning whole table everytime is inefficient, because i expect lot of documents.

alixi•2w ago

how many documents in total do you expect there to be? if it's reasonable one possibility is to not paginate the convex query; instead, return all the results to the user and then do the pagination on the frontend only otherwise the only solution i can think of is to define a new table like

contactSearchFields: defineTable({
  contactId: v.id('contacts'),
  searchFields: v.optional(v.string()),
  organizationId: v.id('organizations'),
  status: v.union(v.literal("active"), v.literal("deleted")),
  tagId: v.id('tags')
})
  .searchIndex('search_fields', { searchField: 'searchFields', filterFields: ['organizationId', 'status', 'tag'] });

contactSearchFields: defineTable({
  contactId: v.id('contacts'),
  searchFields: v.optional(v.string()),
  organizationId: v.id('organizations'),
  status: v.union(v.literal("active"), v.literal("deleted")),
  tagId: v.id('tags')
})
  .searchIndex('search_fields', { searchField: 'searchFields', filterFields: ['organizationId', 'status', 'tag'] });

and create a row in this table for each different tag that a contact has. then to search through contacts, you can paginate and filter by tags in this new table. the downsides are that you have to update this table whenever you insert, patch, or delete a contact, and if you want to add a new search field it will take some additional work

kamal.panaraOP•2w ago

Large data per organisation, like upto from 3K to 500K documents per organisation. Yes I’m currently trying out this approach Using triggers to keep everything in sync

advance filtering

Did you find this page helpful?