DT
Drizzle Team•3mo 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],
  }),
}));
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.
5 Replies
joostschuur
joostschuurOP•3mo ago
I can run db.query.cardsTable.findMany({ with: { vocabulary: { with: { vocabulary } }) and in the resulting rows vocabulary.vocabulary has an array of he actual vocabulary fields, but I'd prefer it just be a single vocabulary entry with the actual array. If I just run db.query.cardsTable.findMany({ with: { vocabulary: true }) , then vocabulary is an array of cardId and vocabularyId.
joostschuur
joostschuurOP•3mo ago
Looks like this is discussed here as a new feature, so for now, I would have to do a wrapper an remove the nesting myself: https://github.com/drizzle-team/drizzle-orm/issues/2933
GitHub
[FEATURE]: API for to 'flatten' response shapes when querying data ...
Describe what you want Overview / Explanation Default aggregation behaviour of Drizzle Queries is to provide nested response objects with the table name of the relation as the key, and an object of...
joostschuur
joostschuurOP•3mo ago
So basically 
const getCardsWithVocabulary = async () => {
  const results = await db.query.cardsTable.findMany({
    with: {
      vocabulary: {
        with: {
          vocabulary: true,
        },
      },
    },
  });


  return results.map(({ vocabulary, ...rest }) => ({
    ...rest,
    vocabulary: vocabulary.map(({ vocabulary }) => ({
      ...vocabulary,
    })),
  }));
};
const getCardsWithVocabulary = async () => {
  const results = await db.query.cardsTable.findMany({
    with: {
      vocabulary: {
        with: {
          vocabulary: true,
        },
      },
    },
  });


  return results.map(({ vocabulary, ...rest }) => ({
    ...rest,
    vocabulary: vocabulary.map(({ vocabulary }) => ({
      ...vocabulary,
    })),
  }));
};
So then each row looks like this: 
 {
    "id": "card-2",
    "source": "good night world",
    "target": "gute nacht welt",
    "pronunciation": "goo-night wo-rld",
    "vocabulary": [
      {
        "id": "vocab-2",
        "source": "world",
        "target": "welt",
        "pronunciation": "wo-rld",
      },
      {
        "id": "vocab-3",
        "source": "good night",
        "target": "gute nacht",
        "pronunciation": "good-night",
      }
    ]
  }
 {
    "id": "card-2",
    "source": "good night world",
    "target": "gute nacht welt",
    "pronunciation": "goo-night wo-rld",
    "vocabulary": [
      {
        "id": "vocab-2",
        "source": "world",
        "target": "welt",
        "pronunciation": "wo-rld",
      },
      {
        "id": "vocab-3",
        "source": "good night",
        "target": "gute nacht",
        "pronunciation": "good-night",
      }
    ]
  }
joostschuur
joostschuurOP•3mo ago
Well, answering more of my own question, looks like through is coming in the Relational API v2 https://github.com/drizzle-team/drizzle-orm/discussions/2316
GitHub
Relational API v2 · drizzle-team drizzle-orm · Discussion #2316
We've launched Drizzle Relational Queries exactly a year ago and it's time to ship a fundamental upgrade. We've gathered a massive amount of valuable feedback from the community and goi...
joostschuur
joostschuurOP•3mo ago
Thank you for coming to my TED talk

Did you find this page helpful?