Sara
Sara•3w ago

How do I search for a value in a table that is related to another table in relationship?

So I have a table called messages and another table called messages members, I'm trying to search for messages that are equal to the search query but at the same time the user is part of? I've tried creating a paginated query:
const channelsWithUser = await ctx.db.query("members")
.withIndex("by_userId", q => q.eq("userId",user._id))
.collect(); // this has the channel id as a coloumn

const allDmsThatMatch = paginator(ctx.db, schema)
.query("directMessages")
.withIndex("by_channelId", (q) => q.eq("channelId", /** I want to put the channelId here */))
.order("desc")
.paginate({numItems:10, cursor:args.searchQuery});
const channelsWithUser = await ctx.db.query("members")
.withIndex("by_userId", q => q.eq("userId",user._id))
.collect(); // this has the channel id as a coloumn

const allDmsThatMatch = paginator(ctx.db, schema)
.query("directMessages")
.withIndex("by_channelId", (q) => q.eq("channelId", /** I want to put the channelId here */))
.order("desc")
.paginate({numItems:10, cursor:args.searchQuery});
And I tried to mix and match using asyncMap with search:
const allDMsThatFit = await asyncMap(channelsWithUser, async (doc) => {
const findDMsThatMatchInChannel = await ctx.db
.query('directMessages')
.withSearchIndex('search_content', (q) =>
q
.search('directMessageContent', args_0.searchQuery)
.eq('directMessageChannelId', doc.directMessageChannelId),
)
.take(5);
return {
findDMsThatMatchInChannel,
channelId: doc.directMessageChannelId,
};
});
const allDMsThatFit = await asyncMap(channelsWithUser, async (doc) => {
const findDMsThatMatchInChannel = await ctx.db
.query('directMessages')
.withSearchIndex('search_content', (q) =>
q
.search('directMessageContent', args_0.searchQuery)
.eq('directMessageChannelId', doc.directMessageChannelId),
)
.take(5);
return {
findDMsThatMatchInChannel,
channelId: doc.directMessageChannelId,
};
});
but got lost on how to handle the channelId side? any advice on how can this be done?
7 Replies
Convex Bot
Convex Bot•3w 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
lee•3w ago
Can you describe a little more of where you got lost? That asyncMap and withSearchIndex look pretty good to me. And I see you're using paginator which is an advanced feature 🙂 If you want to put multiple channel ids in the allDmsThatMatch query, i would put in the cursor some indication of which channel you're looking in, so the pagination can go through the channels one at a time
Sara
SaraOP•3w ago
That's what i initially wanted to do (to use paginator) , but it some how flopped when i was trying to figure out how to search "every channel for that search query" so i went with using an async map and checking for first 5 that match that search query
lee
lee•3w ago
Ah because paginator doesn't support withSearchQuery? Well your solution for that looks pretty good to me
Sara
SaraOP•3w ago
Yep, and i didn't want to use filter
lee
lee•3w ago
Just as another option, you could put the separate channel ids into separate queries and use withSearchQuery in each one. It does seem a bit excessive to stitch together results on the client-side like that, but it would allow you to paginate through all of the results.
Sara
SaraOP•3w ago
oh totally, I'm thinking of using that to specific filtering method since I have added the channel Id to the schema

Did you find this page helpful?