Deleted User
Deleted User
PostsComments
CCConvex Community
Created by Deleted User on 5/21/2024 in #support-community
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...
49 replies