Deleted User•8mo ago

About querying relational data and getting data from joined table fields

I want to query a relational data and retrieve fields from joined table in convex. I have a two tables that have 1:N relation and i want to reach a field at joined table from original table like:

const posts = await db.query.posts.findMany({
  columns: {
    id: true,
    content: true,
  },
  with: {
    comments: {
      columns: {
        authorId: false
      }
    }
  }
});

const posts = await db.query.posts.findMany({
  columns: {
    id: true,
    content: true,
  },
  with: {
    comments: {
      columns: {
        authorId: false
      }
    }
  }
});

I dont know if there is a way to achieve this at convex, but my problem is i need this query to add a document to related table cuz my data comes from a from as string and i cant pass any ID into it. At plain sql its becoming like:

insert into tasks where cat_id = "Cat1"

>> Validation Error: (an id like "0efgehhee33dad" expected, but found "Cat1"

insert into tasks where cat_id = "Cat1"

>> Validation Error: (an id like "0efgehhee33dad" expected, but found "Cat1"

At this schema:

task_categories: defineTable({ name: v.string() }),
tasks: defineTable({
  category_id: v.id("task_categories"),
  desc: v.string(),
  name: v.string(),
  time: v.string(),
}),

task_categories: defineTable({ name: v.string() }),
tasks: defineTable({
  category_id: v.id("task_categories"),
  desc: v.string(),
  name: v.string(),
  time: v.string(),
}),

its not possible to add a task with a string category_name with:

await ctx.db.insert("users", { name: "Task 1", category_id: "???" }) // i only have category_name comes from form...

await ctx.db.insert("users", { name: "Task 1", category_id: "???" }) // i only have category_name comes from form...

18 Replies

Michal Srb•8mo ago

I'm curious if the bot in #ask-ai can answer this. You might want to give it more examples (what data do you have in each table, and what data you want read / written).

Deleted UserOP•8mo ago

there is no data at both tables cuz i cant add any into tasks table due to this reason. Basically what i want, when i create a task i want to insert data into both tasks and categories table for example a mutation like insert into tasks where category = "Cat1"; then i want to see an update at categories table id: 1 | name: "Cat1" but i cant do this cuz there is no field like category at tasks table and schema doesnt allow me to do it

Michal Srb•8mo ago

You need to insert the category first:

export const makeTask = mutation(async ctx => {
  const category_id = await ctx.db.insert("categories", {name: "foo"});
  const task_id = await ctx.db.insert("tasks", {category_id, desc: "bla", name: "da", time: "2020-03-20"});
});

export const makeTask = mutation(async ctx => {
  const category_id = await ctx.db.insert("categories", {name: "foo"});
  const task_id = await ctx.db.insert("tasks", {category_id, desc: "bla", name: "da", time: "2020-03-20"});
});

Deleted UserOP•8mo ago

yea, but definitely at this point things going reverse on my side thats why i confused categories are coming from a form at client side like a json task: { name: "Test", category: "Cat1" } so there is no predefined categories There are categories but coming from form dropdown/combobox I can get categories from table into that form field and can pass into task but there is no field like category at tasks table and cant at typescript its like

type Task = {
  name: string;
  category: "Cat1" | "Cat2" | "Cat3" | ...
  ... rest
}

type Task = {
  name: string;
  category: "Cat1" | "Cat2" | "Cat3" | ...
  ... rest
}

but i only have category name, not id to pass into create mutation

Michal Srb•8mo ago

Why not

export const makeTask = mutation(async ctx => {
  const task_id = await ctx.db.insert("tasks", {category: "Cat1", desc: "bla", name: "da", time: "2020-03-20"});
});

export const makeTask = mutation(async ctx => {
  const task_id = await ctx.db.insert("tasks", {category: "Cat1", desc: "bla", name: "da", time: "2020-03-20"});
});

then? It's not clear what is preventing you from using whichever approach you want to take. If you want to read the category in the mutation:

export const makeTask = mutation(async ctx => {
  const category = await ctx.db.query("categories").filter(q => q.eq(q.field("name"), args.categoryName)).unique();
  const task_id = await ctx.db.insert("tasks", {category_id: category._id, desc: "bla", name: "da", time: "2020-03-20"});
});

export const makeTask = mutation(async ctx => {
  const category = await ctx.db.query("categories").filter(q => q.eq(q.field("name"), args.categoryName)).unique();
  const task_id = await ctx.db.insert("tasks", {category_id: category._id, desc: "bla", name: "da", time: "2020-03-20"});
});

And use an index instead of a filter for better performance.

Michal Srb•8mo ago

https://docs.convex.dev/database/indexes/

Indexes | Convex Developer Hub

Indexes are a data structure that allow you to speed up your

Deleted UserOP•8mo ago

Yep this is what i roughly have, but not sure if its okay or i am wrong i was thinking about having a FK for category_name instead category_id tasks.category_name like so

Michal Srb•8mo ago

I would recommend using IDs as foreign keys. So pass the category IDs to your form field, and when you're creating the task, pass the category ID to createTask.

Deleted UserOP•8mo ago

const addTask = async (formData) => {
    "use server";
    const category = formData.get("category");

    const cat = await ctx.db.get("category").filter((cat.name = category)); // ignore syntax

    await ctx.db.insert("tasks", { name: "name", category_id: cat._id });

    // done
  };

const addTask = async (formData) => {
    "use server";
    const category = formData.get("category");

    const cat = await ctx.db.get("category").filter((cat.name = category)); // ignore syntax

    await ctx.db.insert("tasks", { name: "name", category_id: cat._id });

    // done
  };

So you are saying this is the way to achieve that kinda thing and my schema is correct Say i fixed inserting with this, what about querying same category to show at task card like JIRA I mean i need to access a category name belong to a task

const tasks = await ctx.db.query("tasks").collect()
typeof tasks = {
    _id: Id<"tasks">;
    _creationTime: number;
    name: string;
    category_id: Id<"task_categories">;
    desc: string;
    time: string;
}[]
{tasks.map((item, idx) => {
 <span>{item.category_name // but there is no}</span>

const tasks = await ctx.db.query("tasks").collect()
typeof tasks = {
    _id: Id<"tasks">;
    _creationTime: number;
    name: string;
    category_id: Id<"task_categories">;
    desc: string;
    time: string;
}[]
{tasks.map((item, idx) => {
 <span>{item.category_name // but there is no}</span>

Michal Srb•8mo ago

First of all, you can't access the DB directly from a Node.js action. So I would make a query that lists categories, returning category[] (incl. _id and name) a form field that renders the categories (using name as visible label and _id as the field value) a mutation that accepts the task properties including the selected category _id

Michal Srb•8mo ago

This demo should be somewhat similar (but with channels and messages instead of categories and tasks): https://github.com/get-convex/convex-demos/tree/main/relational-data-modeling

GitHub

convex-demos/relational-data-modeling at main · get-convex/convex-d...

Demo apps built on Convex. Contribute to get-convex/convex-demos development by creating an account on GitHub.

Deleted UserOP•8mo ago

I checked that too and other Ents docs at traditional way there is a thing to do this, i am wondering if convex also have this tasks.category_id = categories.category_id I am in search for this syntax:

const posts = await db.query.posts.findMany({
  columns: {
    id: true,
    content: true,
  },
  with: {
    comments: {
      columns: {
        authorId: false
      }
    }
  }
});

const posts = await db.query.posts.findMany({
  columns: {
    id: true,
    content: true,
  },
  with: {
    comments: {
      columns: {
        authorId: false
      }
    }
  }
});

get all posts with id, content with comments of authorId for ex i want name from tasks table and category_name from categories table as base

Michal Srb•8mo ago

The prisma query would be in vanilla Convex (assuming that "comments" have postId foreign key and index):

export const postsQuery = query(async (ctx) => {
  return await Promise.all(
    (await ctx.db.query("posts").collect()).map(async post => ({
      _id: post._id,
      content: post.content,
      comments: (await ctx.db.query("comments")
        .withIndex("postId", q => q.eq("postId", post._id)
        .collect()).map(({authorId, ...comment}) => comment)
    })
  );
})

export const postsQuery = query(async (ctx) => {
  return await Promise.all(
    (await ctx.db.query("posts").collect()).map(async post => ({
      _id: post._id,
      content: post.content,
      comments: (await ctx.db.query("comments")
        .withIndex("postId", q => q.eq("postId", post._id)
        .collect()).map(({authorId, ...comment}) => comment)
    })
  );
})

With convex-ents:

export const postsQuery = query(async (ctx) => {
  return await ctx.table("posts")
    .map(async post => ({
      _id: post._id,
      content: post.content,
      comments: (await post.edge("comments"))
        .map(({authorId, ...comment}) => comment)
    });
})

export const postsQuery = query(async (ctx) => {
  return await ctx.table("posts")
    .map(async post => ({
      _id: post._id,
      content: post.content,
      comments: (await post.edge("comments"))
        .map(({authorId, ...comment}) => comment)
    });
})

Deleted UserOP•8mo ago

what kind of schema should i define to achieve this queries cuz as i said when i define relations at schema, its not allowing to write queries like that

task_categories: defineTable({ name: v.string() }),
tasks: defineTable({
  category_id: v.id("task_categories"),
  desc: v.string(),
  name: v.string(),
  time: v.string(),
}),

task_categories: defineTable({ name: v.string() }),
tasks: defineTable({
  category_id: v.id("task_categories"),
  desc: v.string(),
  name: v.string(),
  time: v.string(),
}),

Michal Srb•8mo ago

For the vanilla Convex query I wrote above:

defineSchema({
  posts: defineTable({
    content: v.string(),
  }),
  comments: defineTable({
    postId: v.id("posts"),
    authorId: v.id("users"),
  }).index("postId", ["postId"]);
});

defineSchema({
  posts: defineTable({
    content: v.string(),
  }),
  comments: defineTable({
    postId: v.id("posts"),
    authorId: v.id("users"),
  }).index("postId", ["postId"]);
});

if you're encountering an error please share the full error.

Deleted UserOP•8mo ago

actually problem is this for now:

const tasks = await ctx.db.query("tasks").collect()
typeof tasks = {
    _id: Id<"tasks">;
    _creationTime: number;
    name: string;
    category_id: Id<"task_categories">;
    desc: string;
    time: string;
}[]
{tasks.map((item, idx) => {
 <span>{item.category_name // but there is no}</span>

const tasks = await ctx.db.query("tasks").collect()
typeof tasks = {
    _id: Id<"tasks">;
    _creationTime: number;
    name: string;
    category_id: Id<"task_categories">;
    desc: string;
    time: string;
}[]
{tasks.map((item, idx) => {
 <span>{item.category_name // but there is no}</span>

Property 'category_name' does not exist on type '{ _id: Id<"tasks">; _creationTime: number; name: string; category_id: Id<"task_categories">; desc: string; time: string; }'.

error is expected but what is solution to get category_name from tasks in short I have two solutions: - having a category_name in tasks table - reaching category_name from tasks somehow and returning it back and use But i dont know how to do each of those

Michal Srb•8mo ago

You can do a join ("reaching category_name from tasks somehow and returning it back and use"):

export const tasksQuery = query(async (ctx) => {
  return await Promise.all(
    (await ctx.db.query("tasks").collect()).map(async task => ({
      ...task,
      category_name: (await ctx.db.get(task.category_id)).name
    })
  );
})

export const tasksQuery = query(async (ctx) => {
  return await Promise.all(
    (await ctx.db.query("tasks").collect()).map(async task => ({
      ...task,
      category_name: (await ctx.db.get(task.category_id)).name
    })
  );
})

Deleted UserOP•8mo ago

hmm this can solve my problem Thanks for your all support and sorry for my inconvenience. I couldnt continue on my project cuz of this 😦 I should definitely say Convex is a game-changer in web industry, hope it becomes more common soon I used Entity Framework at old times 😄 its now looking like a magic to me

About querying relational data and getting data from joined table fields

Did you find this page helpful?