Spiced•17mo ago

relationships / joins

For example and more clarification this is my query: export const getDiagrams = query({ args: {}, handler: async (ctx) => { const identity = await ctx.auth.getUserIdentity(); if (!identity) { throw new Error("Unauthorized"); } const userId = identity.subject; const dashboard = await ctx.db .query("diagrams") .withIndex("by_user", (q) => q.eq("userId", userId)) .filter((q) => q.eq(q.field("isArchived"), false)) .collect(); return dashboard; }, }); i have a relationship in the entities, linked to the diagramId. Is there any way to retreive them all at once ?

3 Replies

jamwt•17mo ago

yep! see https://stack.convex.dev/relationship-structures-let-s-talk-about-schemas and https://stack.convex.dev/functional-relationships-helpers for some guidance and tools to model and efficiently execution relational queries

Relationship Structures: Let's Talk About Schemas

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

Functional Relationships: Helpers

In this post, we’ll look at some helper functions to help write code to traverse relationships in a readable, predictable, and debuggable way.

SpicedOP•17mo ago

Okay @Jamie im trying to wrap my head around this since im coming from an sql background 😅 .

 js 

export default defineSchema({
  diagrams: defineTable({
    title: v.string(),
    description: v.optional(v.string()),
    userId: v.string(),
    isArchived: v.boolean(),
    isPublished: v.boolean(),
  }).index("by_user", ["userId"]),
  entities: defineTable({
    title: v.string(),
    description: v.optional(v.string()),
    diagramId: v.id("diagrams"),
    xPos: v.number(),
    yPos: v.number(),
  }).index("by_diagramId", ["diagramId"]),
});

 js 

export default defineSchema({
  diagrams: defineTable({
    title: v.string(),
    description: v.optional(v.string()),
    userId: v.string(),
    isArchived: v.boolean(),
    isPublished: v.boolean(),
  }).index("by_user", ["userId"]),
  entities: defineTable({
    title: v.string(),
    description: v.optional(v.string()),
    diagramId: v.id("diagrams"),
    xPos: v.number(),
    yPos: v.number(),
  }).index("by_diagramId", ["diagramId"]),
});

So this is my schema, i have a one to many relationship with all the document. So in theory if i wanted to get all of them, i can do something like:

export const getById = query({
  args: {
    diagramId: v.id("diagrams"),
  },
  handler: async (ctx, args) => {
    const identity = await ctx.auth.getUserIdentity();

    const document = await ctx.db.get(args.diagramId);

    if (!document) {
      throw new Error("Not found");
    }

    if (document.isPublished && !document.isArchived) {
      return document;
    }

    if (!identity) {
      throw new Error("Unauthorized");
    }

    const userId = identity.subject;

    if (document.userId !== userId) {
      throw new Error("Unauthorized");
    }

    var relationships = await getManyFrom(
      ctx.db,
      "entities",
      "diagramId",
      document._id
    );
    const transformedRelationships = relationships.map(
      (relationship: Doc<"entities">) => ({ ...relationship })
    );

    const result = { ...document, entities: transformedRelationships };

    return result;
  },
});

export const getById = query({
  args: {
    diagramId: v.id("diagrams"),
  },
  handler: async (ctx, args) => {
    const identity = await ctx.auth.getUserIdentity();

    const document = await ctx.db.get(args.diagramId);

    if (!document) {
      throw new Error("Not found");
    }

    if (document.isPublished && !document.isArchived) {
      return document;
    }

    if (!identity) {
      throw new Error("Unauthorized");
    }

    const userId = identity.subject;

    if (document.userId !== userId) {
      throw new Error("Unauthorized");
    }

    var relationships = await getManyFrom(
      ctx.db,
      "entities",
      "diagramId",
      document._id
    );
    const transformedRelationships = relationships.map(
      (relationship: Doc<"entities">) => ({ ...relationship })
    );

    const result = { ...document, entities: transformedRelationships };

    return result;
  },
});

but in return, the type of this return is the type of Doc<"diagram"> not the custom result i had let out, which is causing me type issues

mikeysee•17mo ago

@Spiced When I have "type" issues like this I often find it helpful to type the return of the function so you explicitly tell Typescript what you are expecting to return from this. So for example:

export const getById = query({
  args: {
    diagramId: v.id("diagrams"),
  },
  handler: async (ctx, args): Promise<Doc<"entities">[]> => { 
...
},
});

export const getById = query({
  args: {
    diagramId: v.id("diagrams"),
  },
  handler: async (ctx, args): Promise<Doc<"entities">[]> => { 
...
},
});

If you so something like this then it should show that you are returning from the function in multiple places:

if (document.isPublished && !document.isArchived) {
      return document;
    }

if (document.isPublished && !document.isArchived) {
      return document;
    }

and

const result = { ...document, entities: transformedRelationships };
    return result;

const result = { ...document, entities: transformedRelationships };
    return result;

Each of these is going to have different type signatures. Im not sure if that is what you intended?

relationships / joins

Did you find this page helpful?