Drizzle TeamDT
Drizzle Team•14mo ago
Nico

Help with relations: many-to-many + one-to-one

I'm having trouble figuring out how to model the following relationships between papers, authors, and author's names with Drizzle relations. The schema looks like this (I've ommitted some columns for brevity)

export const authors = sqliteTable("authors", (t) => ({
   authorId: t.integer().primaryKey(),
   firstName: t.text(),
   lastName: t.text(),
}));

export const papers = sqliteTable("papers", (t) => ({
   paperId: t.integer().primaryKey(),
}));

export const authorsToPapers = sqliteTable("authors_to_papers", (t) => ({
   authorId: t.integer().notNull().references(() => authors.authorId),
   paperId: t.integer().notNull().references(() => papers.paperId),
}));


Conceptually, the relations are as follows:

  • each row in authors can be related to many rows in papers, and each row in papers can be related to many rows authors (many to many)
  • each row in authors_to_papers is be associated with one row of authors
Here are the relations I have set up:

export const authorsRelations = relations(authors, ({ one, many }) => ({
   authorsToPapers: many(authorsToPapers),
}));

export const papersRelations = relations(papers, ({ many }) => ({
   authorsToPapers: many(authorsToPapers),
}));

export const authorsToPapersRelations = relations(
   authorsToPapers,
   ({ one }) => ({
      author: one(authors, {
         fields: [authorsToPapers.authorId],
         references: [authors.authorId],
      }),
      paper: one(papers, {
         fields: [authorsToPapers.paperId],
         references: [papers.paperId],
      }),
   }),
);


I know that I can do the following to return a paper with it's author(s):

await db.query.papers.findFirst({
   with: {
      authorsToPapers: true,
   },
});


But how do I get from the authorIds returned in the output of the above to the firstName and lastName associated with those authorIds?
Was this page helpful?