grez72•6mo ago

native join

Given that it is possible to add relations to a document, is there a plan for convexdb to introduce a join operation? e.g., something like the following to efficiently retrieve a document and associated documents (further specifying which document fields to include to avoid over fetching).

const myPostsWithCommentsAndCommenterInfo = await ctx.db
      .query("posts")
      .withIndex("by_id_creator", q=> q.eq("id_creator", args.id_me))
      .join("comments").on("posts", "id_comment").as("comment")
      .join("users").on("comment", "id_user").as("comment_by").includes(["username", "profile_photo"])
      .order("desc")
      .paginate(args.paginationOpts)

const myPostsWithCommentsAndCommenterInfo = await ctx.db
      .query("posts")
      .withIndex("by_id_creator", q=> q.eq("id_creator", args.id_me))
      .join("comments").on("posts", "id_comment").as("comment")
      .join("users").on("comment", "id_user").as("comment_by").includes(["username", "profile_photo"])
      .order("desc")
      .paginate(args.paginationOpts)

Without this kind of built in support for joining documents, I'm not sure how to leverage the "relational" aspect of the document store. Given the current convex database query system, what would be the most efficient way to construct the full myPostsWithCommentsAndCommenterInfo document in the example above?

7 Replies

jamwt•6mo ago

hi! yep, people do tons of joins in convex a few bits of reading:

jamwt•6mo ago

https://stack.convex.dev/functional-relationships-helpers

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...

jamwt•6mo ago

people use that a lot. or if you want a boilerplate-elimination syntax, that's especially good at joins... you probably want convex-ents, a higher-level orm thing built on convex: https://labs.convex.dev/convex-ents

Convex Ents - Convex Ents

Relations, default values, unique fields and more for Convex

jamwt•6mo ago

https://labs.convex.dev/convex-vs-prisma?right=convexEnts examples of ents syntax vs. prisma, for common operations

Prisma vs Convex

Compare Prisma examples with Convex

jamwt•6mo ago

here's examples showing off relationship traversals: https://labs.convex.dev/convex-vs-prisma?right=convexEnts&selected=Reading+Data,Traverse+Relations

Prisma vs Convex

Compare Prisma examples with Convex

jamwt•6mo ago

also, convex search will always provide lots of resources: https://search.convex.dev/?q=relationships

Convex Developer Search

Search Docs, Stack, Discord all at once

grez72OP•6mo ago

Super helpful...thanks! 🚀

native join

Did you find this page helpful?