Frost7994 Comments - Answer Overflow

Frost7994

•Created by Frost7994 on 2/11/2025 in #help

Disambiguating relations

@TOSL thanks again for the continued help. That link just takes me to the playground landing, not to a specific playground. Is there a name I can search in the saved playgrounds to access it?

45 replies

DTDrizzle Team

•Created by Frost7994 on 2/11/2025 in #help

Disambiguating relations

import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core'; import { relations } from 'drizzle-orm'; export const users = pgTable('users', { id: serial('id').primaryKey(), name: text('name'), }); export const usersRelations = relations(users, ({ many }) => ({ author: many(posts, { relationName: 'author' }), reviewer: many(posts, { relationName: 'reviewer' }), })); export const posts = pgTable('posts', { id: serial('id').primaryKey(), content: text('content'), authorId: integer('author_id'), reviewerId: integer('reviewer_id'), }); export const postsRelations = relations(posts, ({ one }) => ({ author: one(users, { fields: [posts.authorId], references: [users.id], relationName: 'author', }), reviewer: one(users, { fields: [posts.reviewerId], references: [users.id], relationName: 'reviewer', }), })); Maybe I'm over complicating what I want to achieve with the constraints I want as in not allowing a user to be a student and teacher at the same time as that's something I can manage outside of the database. This is the example in the docs which shows two one to many relations. If we were to use this example what I want to achieve is one to many relation with post and author but a many to many relation with posts and reviewers which is the part I'm struggling with as I know you need to implement a join table in the fashion of reviews to posts and use that but I can't seem to execute that properly

45 replies

DTDrizzle Team

•Created by Frost7994 on 2/11/2025 in #help

Disambiguating relations

I want every class to have many teachers and many students. Each user who is a student, dictated by their role, can only be a student of one class. Each user who is a teacher, again dictated by their role, can be a teacher of many classes. Thanks for the patience and helping me sort this out

45 replies

DTDrizzle Team

•Created by Frost7994 on 2/11/2025 in #help

Disambiguating relations

No a user can only be one or the other, determined by their role. A studentTable and teacherTable would share all the same fields, i.e id, name, email, username, createdAt and updatedAt and this felt repetitive so it made more sense to me to create a user table and differentiate between the students and teachers by giving the user a role of one OR the other.

45 replies

DTDrizzle Team

•Created by Frost7994 on 2/11/2025 in #help

Disambiguating relations

This is exactly what I mean. A user of the platform can be either a teacher or a student. If they're a student they can be a student to one class. If they're a teacher they can teach many classes. As Teacher and Student have all the same fields other than their role I thought it made the most sense to have them as a User table and differentiated by a role enum. I have seen many implementations of something similar which is why it seemed like the best result. If it's incredibly arbitrary and fragile I'd be delighted to know what setup would be better? I am new enough to using SQL hence the issue I'm facing

45 replies

DTDrizzle Team

•Created by Frost7994 on 2/11/2025 in #help

Disambiguating relations

Hi @TOSL thank you for getting back to me! I'm struggling a little to understand what you mean, my current schema is as follows:

export const userRoles = [STUDENT, TEACHER, ADMIN] as const;
export type UserRole = (typeof userRoles)[number];
export const userRoleEnum = pgEnum("role", userRoles);

export const UserTable = pgTable("users", {
  id,
  // add clerk user id here

  firstName: varchar().notNull(),
  lastName: varchar().notNull(),

  username: varchar().unique(),
  role: userRoleEnum().default("student"),

  email: varchar().unique(),

  ...timestamps,
});

export const ClassTable = pgTable("classes", {
  id,
  name: varchar().notNull().unique(),

  ...timestamps,
});

export const userRoles = [STUDENT, TEACHER, ADMIN] as const;
export type UserRole = (typeof userRoles)[number];
export const userRoleEnum = pgEnum("role", userRoles);

export const UserTable = pgTable("users", {
  id,
  // add clerk user id here

  firstName: varchar().notNull(),
  lastName: varchar().notNull(),

  username: varchar().unique(),
  role: userRoleEnum().default("student"),

  email: varchar().unique(),

  ...timestamps,
});

export const ClassTable = pgTable("classes", {
  id,
  name: varchar().notNull().unique(),

  ...timestamps,
});

I'm not sure how to implement the junction tables as you've suggested, each id and timestamp is a helper function to generate an id which is uuid().notNull().primary key() and timestamps creates a createdAt and updatedAt field. If you could help implement your suggestion I'd really appreciate it

45 replies

Gaming

Programming