Drizzle TeamDT
Drizzle Team•11mo ago
joostschuur

Directly accessing array of many to many relationship properties through junction table

I've set up a many-to-many relationship between one or more vocabulary items on one or more cards, but I can't quite seem to figure out how to set up the relations to go through the junction table but not have it appear nested in the results.

Here's the (simplified) schema:

export const cardsTable = sqliteTable('cards', {
  id: text()
    .primaryKey()
    .default(sql`(lower(hex(randomblob(16))))`),

  source: text().notNull(),
  target: text().notNull(),
  pronunciation: text().notNull(),
});

export const cardsRelations = relations(cardsTable, ({ many }) => ({
  vocabulary: many(cardsToVocabularyTable),
}));

export const vocabularyTable = sqliteTable(
  'vocabulary',
  {
    id: text()
      .primaryKey()
      .default(sql`(lower(hex(randomblob(16))))`),

    source: text().notNull(),
    target: text().notNull(),
    pronunciation: text().notNull(),
  },
  (t) => [unique().on(t.source, t.target)]
);

export const vocabularyRelations = relations(vocabularyTable, ({ many }) => ({
  cards: many(cardsToVocabularyTable),
}));

export const cardsToVocabularyTable = sqliteTable(
  'cards_to_vocabulary',
  {
    cardId: text()
      .notNull()
      .references(() => cardsTable.id),
    vocabularyId: text()
      .notNull()
      .references(() => vocabularyTable.id),
  },
  (t) => [primaryKey({ columns: [t.cardId, t.vocabularyId] })]
);

export const cardsToVocabularyRelations = relations(cardsToVocabularyTable, ({ one }) => ({
  card: one(cardsTable, {
    fields: [cardsToVocabularyTable.cardId],
    references: [cardsTable.id],
  }),
  vocabulary: one(vocabularyTable, {
    fields: [cardsToVocabularyTable.vocabularyId],
    references: [vocabularyTable.id],
  }),
}));


Should this be possible in Drizzle? I'm on drizzle-orm 0.39.3 and using local dev Turso with @libsql/client.
Was this page helpful?