Theo's Typesafe Cult•13mo ago

how to create relatedWords relation for each words using drizzle. | DRIZZLE | SQL |

Hi folks, I'm building an online Turkish dictionary using t3 stack with drizzle. I want every word can have related words so that I can show them in related words section when a word searched. What I tried is to create another table called relatedWords that includes an id and relatedWordId in order to create many-to-many relationship between word and relatedWords. Here's what I tried with drizzle schemas and relations:

//schema.ts
 
export const words = pgTable("words", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  phonetic: varchar("phonetic", { length: 255 }),
  root: varchar("root", { length: 255 }),
  attributes: varchar("attributes", { length: 255 }),
  audio: varchar("audio", { length: 255 }),
  createdAt: date("createdAt").notNull(),
  updatedAt: date("updatedAt").notNull(),
});

export const wordsRelations = relations(words, ({ many }) => ({
  wordMeanings: many(meanings),
  wordsToRelatedWords: many(wordsToRelatedWords),
}));

export const relatedWords = pgTable("related_words", {
  id: serial("id").primaryKey(),
  relatedWordId: integer("related_word_id")
    .notNull()
    .references(() => words.id),
});

export const relatedWordsRelations = relations(relatedWords, ({ many }) => ({
  wordsToRelatedWords: many(wordsToRelatedWords),
}));
export const wordsToRelatedWords = pgTable(
  "words_to_related_words",
  {
    wordId: integer("word_id")
      .notNull()
      .references(() => words.id),
    relatedWordId: integer("related_word_id")
      .notNull()
      .references(() => relatedWords.id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.wordId, t.relatedWordId] }),
  })
);

export const wordsToRelatedWordsRelations = relations(
  wordsToRelatedWords,
  ({ one }) => ({
    words: one(words, {
      fields: [wordsToRelatedWords.wordId],
      references: [words.id],
    }),
    relatedWords: one(words, {
      fields: [wordsToRelatedWords.relatedWordId],
      references: [words.id],
    }),
  })
);

//schema.ts
 
export const words = pgTable("words", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  phonetic: varchar("phonetic", { length: 255 }),
  root: varchar("root", { length: 255 }),
  attributes: varchar("attributes", { length: 255 }),
  audio: varchar("audio", { length: 255 }),
  createdAt: date("createdAt").notNull(),
  updatedAt: date("updatedAt").notNull(),
});

export const wordsRelations = relations(words, ({ many }) => ({
  wordMeanings: many(meanings),
  wordsToRelatedWords: many(wordsToRelatedWords),
}));

export const relatedWords = pgTable("related_words", {
  id: serial("id").primaryKey(),
  relatedWordId: integer("related_word_id")
    .notNull()
    .references(() => words.id),
});

export const relatedWordsRelations = relations(relatedWords, ({ many }) => ({
  wordsToRelatedWords: many(wordsToRelatedWords),
}));
export const wordsToRelatedWords = pgTable(
  "words_to_related_words",
  {
    wordId: integer("word_id")
      .notNull()
      .references(() => words.id),
    relatedWordId: integer("related_word_id")
      .notNull()
      .references(() => relatedWords.id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.wordId, t.relatedWordId] }),
  })
);

export const wordsToRelatedWordsRelations = relations(
  wordsToRelatedWords,
  ({ one }) => ({
    words: one(words, {
      fields: [wordsToRelatedWords.wordId],
      references: [words.id],
    }),
    relatedWords: one(words, {
      fields: [wordsToRelatedWords.relatedWordId],
      references: [words.id],
    }),
  })
);

Drizzle studio throws this error: Error: There is not enough information to infer relation "__public__.relatedWords.wordsToRelatedWords" It's been a while since I created SQL relationships last time and I've never done such a relationship like this. Thanks in advance for any help!

2 Replies

NotLuksus•13mo ago

I think you need to change this:

export const wordsToRelatedWordsRelations = relations(
  wordsToRelatedWords,
  ({ one }) => ({
    words: one(words, {
      fields: [wordsToRelatedWords.wordId],
      references: [words.id],
    }),
    relatedWords: one(words, {
      fields: [wordsToRelatedWords.relatedWordId],
      references: [relatedWords.id],
    }),
  })
);

export const wordsToRelatedWordsRelations = relations(
  wordsToRelatedWords,
  ({ one }) => ({
    words: one(words, {
      fields: [wordsToRelatedWords.wordId],
      references: [words.id],
    }),
    relatedWords: one(words, {
      fields: [wordsToRelatedWords.relatedWordId],
      references: [relatedWords.id],
    }),
  })
);

If you want to make it self referecing, which probably is a good idea you can just get rid of the relatedWords table and have word1Id and word2Id (no better name in my head rn haha) and use that instead Would simplify your code a lot

//schema.ts

import { relations } from "drizzle-orm";
import { date, integer, pgTable, primaryKey, serial, varchar } from "drizzle-orm/pg-core";

export const words = pgTable("words", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  phonetic: varchar("phonetic", { length: 255 }),
  root: varchar("root", { length: 255 }),
  attributes: varchar("attributes", { length: 255 }),
  audio: varchar("audio", { length: 255 }),
  createdAt: date("createdAt").notNull(),
  updatedAt: date("updatedAt").notNull(),
});

export const wordsRelations = relations(words, ({ many }) => ({
  wordsToRelatedWords: many(wordsToRelatedWords),
}));

export const wordsToRelatedWords = pgTable(
  "words_to_related_words",
  {
    word1Id: integer("word_1_id")
      .notNull()
      .references(() => words.id),
    word2Id: integer("word_2_id")
      .notNull()
      .references(() => words.id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.word1Id, t.word2Id] }),
  }),
);

export const wordsToRelatedWordsRelations = relations(
  wordsToRelatedWords,
  ({ one }) => ({
    words: one(words, {
      fields: [wordsToRelatedWords.word1Id],
      references: [words.id],
    }),
    relatedWords: one(words, {
      fields: [wordsToRelatedWords.word2Id],
      references: [words.id],
    }),
  }),
);

//schema.ts

import { relations } from "drizzle-orm";
import { date, integer, pgTable, primaryKey, serial, varchar } from "drizzle-orm/pg-core";

export const words = pgTable("words", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  phonetic: varchar("phonetic", { length: 255 }),
  root: varchar("root", { length: 255 }),
  attributes: varchar("attributes", { length: 255 }),
  audio: varchar("audio", { length: 255 }),
  createdAt: date("createdAt").notNull(),
  updatedAt: date("updatedAt").notNull(),
});

export const wordsRelations = relations(words, ({ many }) => ({
  wordsToRelatedWords: many(wordsToRelatedWords),
}));

export const wordsToRelatedWords = pgTable(
  "words_to_related_words",
  {
    word1Id: integer("word_1_id")
      .notNull()
      .references(() => words.id),
    word2Id: integer("word_2_id")
      .notNull()
      .references(() => words.id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.word1Id, t.word2Id] }),
  }),
);

export const wordsToRelatedWordsRelations = relations(
  wordsToRelatedWords,
  ({ one }) => ({
    words: one(words, {
      fields: [wordsToRelatedWords.word1Id],
      references: [words.id],
    }),
    relatedWords: one(words, {
      fields: [wordsToRelatedWords.word2Id],
      references: [words.id],
    }),
  }),
);

Not sure but that should work, I didnt test

Furki4_4OP•13mo ago

export const wordsToRelatedWordsRelations = relations(
  wordsToRelatedWords,
  ({ one }) => ({
    words: one(words, {
      fields: [wordsToRelatedWords.wordId],
      references: [words.id],
    }),
    relatedWords: one(relatedWords, { // should be related words
      fields: [wordsToRelatedWords.relatedWordId],
      references: [relatedWords.relatedWordId], // changed as you did
    }),
  })
);

export const wordsToRelatedWordsRelations = relations(
  wordsToRelatedWords,
  ({ one }) => ({
    words: one(words, {
      fields: [wordsToRelatedWords.wordId],
      references: [words.id],
    }),
    relatedWords: one(relatedWords, { // should be related words
      fields: [wordsToRelatedWords.relatedWordId],
      references: [relatedWords.relatedWordId], // changed as you did
    }),
  })
);

I made this changes but it's still throwing the same error a word can have multiple related words, like it can be 2, 3, 10 or more. i guess word1Id and word2Id wouldn't work in this cases. one second thought, what if I just put the related words as an array of strings or without any relationship ? is it too bad idea creepysweat

thank you for your time and answer creepyheart

Gaming

Programming

how to create relatedWords relation for each words using drizzle. | DRIZZLE | SQL |