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

[gray carbon]

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});

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,
      };
    });

but got lost on how to handle the channelId side? any advice on how can this be done?

[modest salmon]

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](https://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!

[analog jackal]

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

[gray carbon]

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

[analog jackal]

Ah because paginator doesn't support withSearchQuery? Well your solution for that looks pretty good to me

[gray carbon]

Yep, and i didn't want to use filter

[analog jackal]

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.