Drizzle Team•11mo ago

Not enough information to infer relation

I’m having an issue with my schema that is a migration from Prisma. For this particular issue, I have a job table, and a candidates table, each of them has a many relation to the other, so a job can have many candidates, and a candidate can have many jobs. When I try and run my project, I’m getting “There is not enough information to infer relation “job.candidates”.” Here’s my schema, can’t figure out what I’m doing wrong:

export const candidates = mysqlTable(
  'candidates',
  {
    id: bigint('id', { mode: 'number' }).primaryKey().autoincrement(),
    user_id: varchar('user_id', { length: 255 }).notNull(),
    job_id: bigint('job_id', { mode: 'number' }).notNull()
  },
  (table) => ({
    jobIdIdx: index('candidates_jobId_idx').on(table.job_id),
    userIdIdx: index('candidates_userId_idx').on(table.user_id)
  })
)

export const candidatesRelations = relations(candidates, ({ one, many }) => ({
  user: one(users, { fields: [candidates.user_id], references: [users.id], relationName: 'candidateToUser' }),
  jobs: many(job, { relationName: 'candidatesToJob' })
}))

export const job = mysqlTable(
  'job',
  {
    id: bigint('id', { mode: 'number' }).primaryKey().autoincrement(),
    title: text('title').notNull(),
    user_id: varchar('user_id', { length: 255 }).notNull(),
    team_id: bigint('team_id', { mode: 'number' }).notNull()
  },
  (table) => ({
    userIdIdx: index('jobs_userId_idx').on(table.user_id),
    teamIdIdx: index('jobs_teamId_idx').on(table.team_id)
  })
)

export const jobRelations = relations(job, ({ one, many }) => ({
  user: one(users, { fields: [job.user_id], references: [users.id], relationName: 'user' }),
  candidates: many(candidates, { relationName: 'candidatesToJob' }),
  tags: many(tags, { relationName: 'tags' }),
  job_activity: many(jobActivity, { relationName: 'job_activity' }),
  comments: many(jobComments, { relationName: 'job_comments' })
}))

export const candidates = mysqlTable(
  'candidates',
  {
    id: bigint('id', { mode: 'number' }).primaryKey().autoincrement(),
    user_id: varchar('user_id', { length: 255 }).notNull(),
    job_id: bigint('job_id', { mode: 'number' }).notNull()
  },
  (table) => ({
    jobIdIdx: index('candidates_jobId_idx').on(table.job_id),
    userIdIdx: index('candidates_userId_idx').on(table.user_id)
  })
)

export const candidatesRelations = relations(candidates, ({ one, many }) => ({
  user: one(users, { fields: [candidates.user_id], references: [users.id], relationName: 'candidateToUser' }),
  jobs: many(job, { relationName: 'candidatesToJob' })
}))

export const job = mysqlTable(
  'job',
  {
    id: bigint('id', { mode: 'number' }).primaryKey().autoincrement(),
    title: text('title').notNull(),
    user_id: varchar('user_id', { length: 255 }).notNull(),
    team_id: bigint('team_id', { mode: 'number' }).notNull()
  },
  (table) => ({
    userIdIdx: index('jobs_userId_idx').on(table.user_id),
    teamIdIdx: index('jobs_teamId_idx').on(table.team_id)
  })
)

export const jobRelations = relations(job, ({ one, many }) => ({
  user: one(users, { fields: [job.user_id], references: [users.id], relationName: 'user' }),
  candidates: many(candidates, { relationName: 'candidatesToJob' }),
  tags: many(tags, { relationName: 'tags' }),
  job_activity: many(jobActivity, { relationName: 'job_activity' }),
  comments: many(jobComments, { relationName: 'job_comments' })
}))

4 Replies

Marťafiixek•11mo ago

You want to have many to many relation yet you have no join table That's basic SQL design you are missing on You should create a table called candidates_jobs, where you will have columns candidate_id and job_id There will be one to many relationship between the join table and candidates, and one to many between the join table and jobs Which will eventually form many to many relationship

thejessewintonOP•11mo ago

Do you have an example? I'm trying to build out a new table based on what you're saying here and it's coming back with the same error.

Marťafiixek•11mo ago

Sure I do I have another idea though Watch this YT video

Marťafiixek•11mo ago

https://www.youtube.com/watch?v=QdKdvb6iIXs&t=497s

Sakura Dev

YouTube

Drizzle ORM #7- Many-To-Many Relations ⭐

❤️Please Support me by subscribing to my channel 👉🏻https://www.youtube.com/@sakuradev?sub_confirmation=1 Many-To-Many Relations has Always been tricky but In this episode of Drizzle ORM Comprehensive Course, You will Master how to create Many-To-Many Relations with Drizzle ORM. 📖 Drizzle ORM Playlist: https://www.youtube.com/playlist?list=PLhn...

Gaming

Programming

Not enough information to infer relation