Drizzle Team•9mo ago

Relations between tables unclear

Consider the following schema where a User can create a Post, and each post can have multiple versions of its content. What is the best way for a Post to store the latestVersionId for quick access. I'm having trouble figuring that out, and also how to create a Post since a Post would need a PostVersion first, but a PostVersion needs a Post to link them together.

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),

  authorId: integer("author_id").notNull(),
  parentPostId: integer("parent_post_id"),
  latestVersionId: integer("latest_version_id") // Is this the best way to store a reference to the latest post_version?
    .notNull() // It doesn't make sense for the latestVersionId to be null, so I set it as notNull. But in this case, how can I create a Post or a PostVersion? Either of these need the other during their creation
    .references(() => recordVersions.id),
});

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [records.authorId],
    references: [users.id],
  }),
  parentPost: one(posts, {
    fields: [posts.parentPostId],
    references: [posts.id],
  }),
  versions: many(postVersions),
}));

export const postVersions = pgTable("post_versions", {
  id: serial("id").primaryKey(),

  authorId: integer("author_id").notNull(),
  postId: integer("postId").notNull(),

  content: jsonb("content"),
});

export const postVersionsRelations = relations(postVersions, ({ one }) => ({
  author: one(users, {
    fields: [postVersions.authorId],
    references: [users.id],
  }),
  post: one(posts, {
    fields: [postVersions.postId],
    references: [posts.id],
  }),
}));

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),

  authorId: integer("author_id").notNull(),
  parentPostId: integer("parent_post_id"),
  latestVersionId: integer("latest_version_id") // Is this the best way to store a reference to the latest post_version?
    .notNull() // It doesn't make sense for the latestVersionId to be null, so I set it as notNull. But in this case, how can I create a Post or a PostVersion? Either of these need the other during their creation
    .references(() => recordVersions.id),
});

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [records.authorId],
    references: [users.id],
  }),
  parentPost: one(posts, {
    fields: [posts.parentPostId],
    references: [posts.id],
  }),
  versions: many(postVersions),
}));

export const postVersions = pgTable("post_versions", {
  id: serial("id").primaryKey(),

  authorId: integer("author_id").notNull(),
  postId: integer("postId").notNull(),

  content: jsonb("content"),
});

export const postVersionsRelations = relations(postVersions, ({ one }) => ({
  author: one(users, {
    fields: [postVersions.authorId],
    references: [users.id],
  }),
  post: one(posts, {
    fields: [postVersions.postId],
    references: [posts.id],
  }),
}));

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Relations between tables unclear