Joining related tables help

There is a rough example of a join function in the convex docs that looks like this.

export const eventAttendees = query({
  args: { eventId: v.id("events") },
  handler: async (ctx, args) => {
    const event = await ctx.db.get(args.eventId);
    return Promise.all(
      (event?.attendeeIds ?? []).map((userId) => ctx.db.get(userId)),
    );
  },
});

I have a devices table that is related to a devices_makes and a devices_models table all by _id. Thanks to @lee 's help I got my foreign keys updated with migrations. No I just need help with how to join them so that I get something back like | devices:Asset Tag | devices_makes:Make | devices_model:Model | In the cited example an event can have many attendees (one-to-many). In this case mine are one-to-one. I've tried to plug in my own table and field names for just linking device_makes into the above examples but I get TypeScript Errors. I need some baby steps for join statements. Note to super Convex documentation people: it would be really helpful in the docs if you could include things like example schemas when giving example statements, especially for more complex things like joins.

26 Replies

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

ian•2w ago

Have you read https://stack.convex.dev/relationship-structures-let-s-talk-about-schemas and https://stack.convex.dev/functional-relationships-helpers yet?

Relationship Structures: Let's Talk About Schemas

In this post we’ll look at some patterns for structuring relationships in the Convex database.

Database Relationship Helpers

Traverse database relationships in a readable, predictable, and debuggable way. Support for one-to-one, one-to-many, and many-to-many via utility func...

Corbi-Wan KenobiOP•2w ago

A few weeks ago before I had my keys straightened out. I will revisit it. Does this change if I'm working with TanStack? The data will end up in a TanStack paginated table. My guess is no, since the the route displaying the table calls whatever query function I use in people.ts to get the data and returns it. The table bits aren't directly querying the data. I will say I'm sure I will be coming up on a point (early days of learning both) when I will need clarification on when to use "pure" convex statements vs convex's tanstack query subset (if I read the docs right).

erquhart•2w ago

You should only need to drop into the convex client directly for functionality that isn't available through the tanstack query variant.

Corbi-Wan KenobiOP•2w ago

I'm guessing trying to use the Relationship Helpers would qualify? Unless there is a TanStack Query way of doing the same thing? The Database Relationsal Helpers and docs. OK, I'm really trying but again I think there are some pieces missing in the docs that might be obvious to others especially as regards what code is surrounding the example const statements. Here is my schema. const schema = defineSchema({ people: defineTable({ name_first: v.string(), name_last: v.string(), }), personEmails: defineTable({ email_address: v.string(), primary_email: v.boolean(), peopleId: v.id('people'), emailType: v.id('emailTypes'), }) .index("by_email_address", ["email_address"]), emailTypes: defineTable({ email_type: v.string(), id: v.string(), }), }); What would it look like if I wanted all people with the email address where primary = true and includes the type for the address?

erquhart•2w ago

Can you share an example of what you're trying to do? Is there a specific helper you're trying to use?

Corbi-Wan KenobiOP•2w ago

See what I just posted. Something that would help in the docs is if the captions on the diagrams specified what was being referred to based on the schema. Right now they are just "circles pointing to other circles." In the examples it shows await getManyFrom(db... Am I to assume that should be ctx.db and the whole thing is wrapped in a handler? Because if I just const posts = await getManyFrom(db, "posts", "by_authorId", author._id); in a posts.ts file it gets all kinds of errors (assuming I have a schema that matched the examepl). This is what I mean by it would be better if the documentation were more comprehensive/fleshed out in its use of examples. Give the blog posts example in the docs, what would a complete users.ts or a complete posts.ts file look like, with all of the imports, handler wraper with args and ctx included?

erquhart•2w ago

Gotcha, understand the note on docs improvements. Just want to make sure there's nothing else here you're actively looking for help on.

Corbi-Wan KenobiOP•2w ago

@erquhart Look back a few posts, I posted my schema and a specific example

erquhart•2w ago

I didn't realize you edited your message to include "What would it look like if I wanted all people with the email address where primary = true and includes the type for the address?", got it new message is better lol looking at this now

Corbi-Wan KenobiOP•2w ago

Thanks, I was mid edit when lunch hit. you know, priorities. 🙂

erquhart•2w ago

For each person you would get the emails that have the people id, and map over those emails to get the email types. If your email types are a finite set of specific strings, I'd consider just making type a field on your emails table with v.union(v.literal('foo'), v.literal('bar'), ...)

Corbi-Wan KenobiOP•2w ago

Shouldn't I be able to bridge these three tables with a getManyVia? For now lets say I just want all the people and the associated email marked as primary. What does that look like using Database Relational Helpers? @ian can you chime in on this thread now that I have posted a schema and a specific use case?

erquhart•2w ago

Working backward from the relationship helpers doc linked above, the longer version would look like this:

const emails = await getManyFrom(db, "personEmails", "peopleId", person._id);
const emailTypeIds = emails.map(email => email.emailType);
const emailTypes = await getAll(db, emailTypeIds);

const emails = await getManyFrom(db, "personEmails", "peopleId", person._id);
const emailTypeIds = emails.map(email => email.emailType);
const emailTypes = await getAll(db, emailTypeIds);

So getManyVia would look like:

const emailTypes = await getManyVia(db, "personEmails", "emailType", "peopleId", person._id);

const emailTypes = await getManyVia(db, "personEmails", "emailType", "peopleId", person._id);

But that's not what you want, you want the people with this email data mapped on. So I don't think getManyVia is what you're looking for.

Corbi-Wan KenobiOP•2w ago

You know, I think I figured out part of my problem. In some parts of the docs there is given a block of code that is the longer non-helpers version of a query followed by one-liner equivalent that is the helpers version of the same thing. In other parts, there is a longer block given, but the one-liner that follows is a substitute for the last line only of the longer block, not the entire block. And yeah, I wasn't 100% on the getManyVia but it looked like the closest. I took Spanish in High School, wasn't great at it. I took French in college and could only remember more Spanish that I'd forgotten than I could learn French. That's what this feels like. Coming from SQL the relative simplicity of a one-liner select statement is easy and fresh. Still trying to bridge this gap and I really appreciate your patience and help. @erquhart Is the convention in the helpers that the const thisRightHere should be the same as the name of a table being referenced? const people means the statement is referring to the people table?

erquhart•2w ago

I honestly haven't used the helpers a ton, but in general eg., people would be an array of documents from the table, while person would be a single document from that table.

Corbi-Wan KenobiOP•2w ago

I'm rereading Ian's post on Stack right now. It's fleshed a few things out better that I'm trying to piece together.

erquhart•2w ago

Have you built out a project or done the tutorial yet? It may go a long way to understand the basics, I think the helpers may actually complicate the mental model a bit if you start there.

Corbi-Wan KenobiOP•2w ago

I'm using TanStack Start (which convex has a module that is a mini-TanStackQuery) as my framework and the convex docs related to that are pretty bare bones, so no I have not done the chap app tutorial yet as it seemed I would need to use a different framework. But it looks like a pure React app anyway so maybe its moot. Trying not to mix too much stuff into my tiny brain. But I might do it now that you've mentioned it just to take a break and glean some pieces.

erquhart•2w ago

If you do the main tutorial, the knowledge from that will apply very well for any app you build with Convex, the framework stuff is just a thin layer on top. https://docs.convex.dev/tutorial/

Convex Tutorial: A Chat App | Convex Developer Hub

Convex provides you with a fully featured backend with cloud functions,

ian•2w ago

I only skimmed this since it seems Shawn did a great job so far. The one thing that stands out to me worth calling out is that TanStack is only a React / client-side thing. Once you're in the convex/ directory it's server-side, and those same query/mutation/action functions can be used by any client - React (TanStack or vanilla or ...), python, etc. So the helpers are applicable regardless. And I'd agree that the helpers are nice one-liners to get started with, but most of the time I personally just write the raw statements

Corbi-Wan KenobiOP•2w ago

That was very helpful. Some of the 10,000ft level things make more sense now. My main obstacle is understanding how to write one (or more) statements that give me a joined result. @ian could you look at this post here from above and tell me what you would do? Again, doing these things progamatically(?) and not in an ORM or more SQL way is new to me. But once I see a complete end-to-end working example I'm usually pretty good and translating that to other use cases. https://discord.com/channels/1019350475847499849/1332146850546847764/1332438783609667607

erquhart•2w ago

What you're trying to do is pretty straightforward as joins go. Here's how you'd do the paginated query with joins as you described - no helpers, just queries and mapping:

export const peopleWithEmail = query({
  args: {
    paginationOpts: paginationOptsValidator,
  },
  handler: async (ctx, args) => {
    const result = await ctx.db.query('people').paginate(args.paginationOpts)
    const page = await Promise.all(
      result.page.map(async (person) => {
        const emails = await ctx.db
          .query('personEmails')
          .withIndex('by_peopleId', (q) => q.eq('peopleId', person._id))
          .collect()
        const emailsWithTypes = await Promise.all(
          emails.map(async (email) => {
            const emailType = await ctx.db.get(email.emailType)
            return { ...email, emailType }
          }),
        )
        return { ...person, emails: emailsWithTypes }
      }),
    )
    return { ...result, page }
  },
})

export const peopleWithEmail = query({
  args: {
    paginationOpts: paginationOptsValidator,
  },
  handler: async (ctx, args) => {
    const result = await ctx.db.query('people').paginate(args.paginationOpts)
    const page = await Promise.all(
      result.page.map(async (person) => {
        const emails = await ctx.db
          .query('personEmails')
          .withIndex('by_peopleId', (q) => q.eq('peopleId', person._id))
          .collect()
        const emailsWithTypes = await Promise.all(
          emails.map(async (email) => {
            const emailType = await ctx.db.get(email.emailType)
            return { ...email, emailType }
          }),
        )
        return { ...person, emails: emailsWithTypes }
      }),
    )
    return { ...result, page }
  },
})

Note, you'll need to add that by_peopleId index to your personEmails table.

ian•2w ago

Feel free to check out this repo if you want to see some recent-ish convex code (check the convex/ directory especially): https://github.com/ianmacartney/mid-embeddings/tree/main Not the simplest codebase but should have a ton of patterns represented from various helpers (though not the relationship helpers much except maybe getOrThrow / asyncMap)

GitHub

GitHub - ianmacartney/mid-embeddings: The game of mixed meaning (fo...

The game of mixed meaning (for people who love venn diagrams)... aka using linear algebra on LLM embeddings - ianmacartney/mid-embeddings

ian•2w ago

Also claude-3.5-sonnet does a great job talking through queries (though beware any time it does filter instead of withIndex). Mike did a post on optimizing Cursor for Convex: https://stack.convex.dev/6-tips-for-improving-your-cursor-composer-and-convex-workflow

6 Tips for improving your Cursor Composer and Convex Workflow

After much experimentation I have found some good ways to improve the coding experience when using Cursor and Convex. In this article I share 6 tips t...

Corbi-Wan KenobiOP•2w ago

@ian @erquhart Thank you both. I need a nap now. 🙂

Joining related tables help

Did you find this page helpful?