bmccormick•2mo ago

Question about how to structure queries

Hi - i was wondering what kind of optimizations you do on the backend (i.e., how dumb can i be?) Say i have 3 tables: posts, tags, and postTagAssociation. A post can have 1 to many tags associated to it. Say i want to display all posts along with their tags on the page. I know this is an extreme example - but could i do this? Will this be optimized on the backend?

export const get = query({
    args: {},
    handler: async (ctx) => {
        const posts = await ctx.db.query('Posts').collect();

        return await Promise.all(
            posts.map(async (post) => {
                const tags = await ctx.db
                    .query('PostTagAssociation')
                    .withIndex('by_post', (t) => t.eq('postId', post._id)
                    .collect();

                const tagNames = await Promise.all(
                    tags.map(async (t) => {
                        if (!t.tagId) return null;
                        return await ctx.db.get(t.tagId);
                    })
                );

                return { ...post, tags: tagNames.filter(Boolean) };
            })
        );
    }
});

export const get = query({
    args: {},
    handler: async (ctx) => {
        const posts = await ctx.db.query('Posts').collect();

        return await Promise.all(
            posts.map(async (post) => {
                const tags = await ctx.db
                    .query('PostTagAssociation')
                    .withIndex('by_post', (t) => t.eq('postId', post._id)
                    .collect();

                const tagNames = await Promise.all(
                    tags.map(async (t) => {
                        if (!t.tagId) return null;
                        return await ctx.db.get(t.tagId);
                    })
                );

                return { ...post, tags: tagNames.filter(Boolean) };
            })
        );
    }
});

3 Replies

Convex Bot•2mo 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!

erquhart•5w ago

This is actually fully idiomatic Convex code (except you'll probably want an index on the initial posts query). Your promise.all is the correct way to parallelize, and Convex does optimize this to ensure proper throughput so you don't end up with 1000 queries initializing simultaneously. There's a convex helper asyncMap that does exactly this, just a tad shorter. The only thing to be aware of is expected query output. For example, if you have 500 posts and for some wild reason they each have 100 tags, this will crash. So you'll need to do some common sense things around those scenarios. You'll also need to know if/when pagination is necessary, again based on your own knowledge of how many records a query will return. I've also done things similar to getting all tags (because I know there's less than 100, for example) beforehand to avoid fetching the same data over and over from the db. Caching within function should mostly prevent this, but depending on how the parallelization goes, I think some duplication is still possible there.

bmccormickOP•5w ago

cool thanks. i'll check out asyncMap as well. i also read through some more documentation and found this. Don't know if it is any more performant though.

export const getAll = query({
    args: {},
    handler: async (ctx) => {
        const posts = await ctx.db.query('Posts').collect();

        return await Promise.all(
            posts.map(async (post) => {
                const categories = await getManyVia(
                    ctx.db,
                    'PostCategories',
                    'categoryId',
                    'by_post',
                    post._id,
                    'postId'
                );

                return { ...post, categories: categories.filter(Boolean) };
            })
        );
    }
});

export const getAll = query({
    args: {},
    handler: async (ctx) => {
        const posts = await ctx.db.query('Posts').collect();

        return await Promise.all(
            posts.map(async (post) => {
                const categories = await getManyVia(
                    ctx.db,
                    'PostCategories',
                    'categoryId',
                    'by_post',
                    post._id,
                    'postId'
                );

                return { ...post, categories: categories.filter(Boolean) };
            })
        );
    }
});

Question about how to structure queries

Did you find this page helpful?