David Alonso
David Alonso2w ago

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
Convex Bot
Convex Bot2w 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
lee2w ago
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 work
David Alonso
David AlonsoOP2w ago
I'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 results
lee
lee2w ago
Makes 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 withSearchIndexs or denormalizing fields with a trigger
David Alonso
David AlonsoOP2w ago
i wonder if an algolia component is something that you or anyone has considered
jamwt
jamwt2w ago
yes! algolia or elasticsearch is definitely two top contenders for something we'll want eventually
deen
deen2w ago
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
lee
lee2w ago
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