Drizzle Team•3mo ago

how to do one to many?

hello everyone i am having some problems doing a one to many relation and select on a post schema i have. for context i get this error when ever i try to make a query using the where and with

 Internal error: TypeError: Cannot read properties of undefined (reading 'referencedTable')

 Internal error: TypeError: Cannot read properties of undefined (reading 'referencedTable')

here is my select statement

  const post = await database.query.posts.findFirst({
    where: eq(posts.id, params.postId),
    with: {
      tags: true,
      links: true,
    },
  });

  const post = await database.query.posts.findFirst({
    where: eq(posts.id, params.postId),
    with: {
      tags: true,
      links: true,
    },
  });

and here is my schema:

5 Replies

Deleted_user_f8439ab4ed59OP•3mo ago

 export const tags = pgTable("tags", {
  id: text("id").primaryKey(),
  postId: integer("post_id"),
  tag: text("tag").notNull(),
});
export type TagType = typeof tags.$inferInsert;
export const links = pgTable("links", {
  id: text("id").primaryKey(),
  postId: integer("post_id"),
  url: text("url").notNull(),
});
export type linkType = typeof links.$inferInsert;
export const posts = pgTable(
  "posts",
  {
    id: text("id").primaryKey(),
    userId: text("user_id").notNull(),
    title: text("title").notNull(),
    featured: boolean("featured"),
    image: text("image"),
    excerpt: text("excerpt").notNull(),
    content: text("content").notNull(),
    status: text("status", { enum: ["draft", "published"] })
      .default("draft")
      .notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { mode: "date" }).$onUpdate(
      () => new Date()
    ),
  },
  (t) => ({
    userIdx: index("post_user_idx").on(t.userId),
    createdAtIdx: index("post_created_at_idx").on(t.createdAt),
  })
);

export const linksRelations = relations(links, ({ one }) => ({
  post: one(posts, {
    fields: [links.postId],
    references: [posts.id],
  }),
}));
export const tagsRelations = relations(tags, ({ one }) => ({
  post: one(posts, {
    fields: [tags.postId],
    references: [posts.id],
  }),
}));
export const postRelations = relations(posts, ({ one, many }) => ({
  user: one(users, {
    fields: [posts.userId],
    references: [users.id],
  }),
  tags: many(tags),
  links: many(links),
}));
export type PostType = typeof posts.$inferInsert;

 export const tags = pgTable("tags", {
  id: text("id").primaryKey(),
  postId: integer("post_id"),
  tag: text("tag").notNull(),
});
export type TagType = typeof tags.$inferInsert;
export const links = pgTable("links", {
  id: text("id").primaryKey(),
  postId: integer("post_id"),
  url: text("url").notNull(),
});
export type linkType = typeof links.$inferInsert;
export const posts = pgTable(
  "posts",
  {
    id: text("id").primaryKey(),
    userId: text("user_id").notNull(),
    title: text("title").notNull(),
    featured: boolean("featured"),
    image: text("image"),
    excerpt: text("excerpt").notNull(),
    content: text("content").notNull(),
    status: text("status", { enum: ["draft", "published"] })
      .default("draft")
      .notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { mode: "date" }).$onUpdate(
      () => new Date()
    ),
  },
  (t) => ({
    userIdx: index("post_user_idx").on(t.userId),
    createdAtIdx: index("post_created_at_idx").on(t.createdAt),
  })
);

export const linksRelations = relations(links, ({ one }) => ({
  post: one(posts, {
    fields: [links.postId],
    references: [posts.id],
  }),
}));
export const tagsRelations = relations(tags, ({ one }) => ({
  post: one(posts, {
    fields: [tags.postId],
    references: [posts.id],
  }),
}));
export const postRelations = relations(posts, ({ one, many }) => ({
  user: one(users, {
    fields: [posts.userId],
    references: [users.id],
  }),
  tags: many(tags),
  links: many(links),
}));
export type PostType = typeof posts.$inferInsert;

any help would be very helpful thank you!

Mario564•3mo ago

Hi there. Your schema, or at least from what I can see of it, looks good. Is there more to the error message, like a stack trace?

Deleted_user_f8439ab4ed59OP•3mo ago

let me take a look! here is the full error message: ⨯ Internal error: TypeError: Cannot read properties of undefined (reading 'referencedTable') at normalizeRelation (./node_modules/drizzle-orm/relations.js:211:118) at PgDialect.buildRelationalQueryWithoutPK (./node_modules/drizzle-orm/pg-core/dialect.js:977:101) at QueryPromise._getQuery (./node_modules/drizzle-orm/pg-core/query-builders/query.js:91:25) at QueryPromise._toSQL (./node_modules/drizzle-orm/pg-core/query-builders/query.js:106:24) at eval (./node_modules/drizzle-orm/pg-core/query-builders/query.js:69:42) at Object.startActiveSpan (./node_modules/drizzle-orm/tracing.js:14:14) at QueryPromise._prepare (./node_modules/drizzle-orm/pg-core/query-builders/query.js:68:60) at eval (./node_modules/drizzle-orm/pg-core/query-builders/query.js:115:19) at Object.startActiveSpan (./node_modules/drizzle-orm/tracing.js:14:14) at QueryPromise.execute (./node_modules/drizzle-orm/pg-core/query-builders/query.js:114:60) at QueryPromise.then (./node_modules/drizzle-orm/query-promise.js:26:17) digest: "88883804" ⨯ Internal error: TypeError: Cannot read properties of undefined (reading 'referencedTable') at normalizeRelation (./node_modules/drizzle-orm/relations.js:211:118) at PgDialect.buildRelationalQueryWithoutPK (./node_modules/drizzle-orm/pg-core/dialect.js:977:101) at QueryPromise._getQuery (./node_modules/drizzle-orm/pg-core/query-builders/query.js:91:25) at QueryPromise._toSQL (./node_modules/drizzle-orm/pg-core/query-builders/query.js:106:24) at eval (./node_modules/drizzle-orm/pg-core/query-builders/query.js:69:42) at Object.startActiveSpan (./node_modules/drizzle-orm/tracing.js:14:14) at QueryPromise._prepare (./node_modules/drizzle-orm/pg-core/query-builders/query.js:68:60) at eval (./node_modules/drizzle-orm/pg-core/query-builders/query.js:115:19) at Object.startActiveSpan (./node_modules/drizzle-orm/tracing.js:14:14) at QueryPromise.execute (./node_modules/drizzle-orm/pg-core/query-builders/query.js:114:60) at QueryPromise.then (./node_modules/drizzle-orm/query-promise.js:26:17) digest: "88883804" GET /dashboard/editor/23423 500 in 16531ms is it because i have to add an refernece to my post schema perhaps? im actually unsure how to do this the doc's are very bad at explaining in step to step how to set up one to many okay so i found a solution: const post = await database .select() .from(posts) .where(eq(posts.id, params.postId)) .leftJoin(tags, eq(tags.postId, params.postId)) .leftJoin(links, eq(links.postId, params.postId)); i had to do a leftJoin but that kinda defeat the purpose

Mario564•3mo ago

Hmm, it might be that there's something wrong with a relation that I can't see in the code you provided. A way I verify that my relations are valid is by running Drizzle Studio, it will error if there's an invalid relation in your schema

Deleted_user_f8439ab4ed59OP•3mo ago

yeah but my drizzle studio seems to work fine

Gaming

Programming

how to do one to many?