Full-text search across fields in different tables
I'm wondering what the easiest way is to do this?
I assume I can just create a search index on a new table and use triggers to denormalize data, but maybe there's a better way
8 Replies
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!
I would start with two
withSearchIndex
calls in the same convex query, and merge the results. Curious if there's a scenario where that wouldn't workI'm not super familiar with
withSearchIndex
but if this returns a similarity score i guess it should work. Main issue i see is with pagination and correctly ranking resultsMakes sense. I don't think the score is returned so merging would be a matter of counting occurrences, and for pagination i would just use
.take
(paginating on text search results isn't super useful since you can only look up 1024 results anyway)
I don't know of a better way than two withSearchIndex
s or denormalizing fields with a triggeri wonder if an algolia component is something that you or anyone has considered
yes! algolia or elasticsearch is definitely two top contenders for something we'll want eventually
Could the score be exposed? I ended up throwing a few fields into a big text blob because the merged results obviously varied in quality, and I didn't want to run my own search on top of that. These workarounds aren't terrible or anything, but it'd be a nice stopgap until a more advanced solution is available
one problem with exposing score is the score depends on entire-table-statistics. You may notice when doing .withSearchIndex().paginage() , which paginates over the score, that query results can change drastically when the table changes. So merging scores between tables probably won't work very well