Drizzle Team•2mo ago

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),
}));

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],
      }),
   }),
);

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,
   },
});

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?

3 Replies

rphlmr ⚡•2mo ago

👋 maybe like that? https://drizzle.run/k8ctyqfcoen0gepuz9ma2g3p

Drizzle Run

sqlite relations example - Drizzle Run

rphlmr ⚡•2mo ago

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

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

NicoOP•2mo ago

Yes this worked! Thank you so much! I thought I had tried that but it didn't work.

Gaming

Programming

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