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

relatedWords

relatedWords

relatedWords that includes an

id

id

id

id and

relatedWordId

relatedWordId

relatedWordId

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

//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"

Error: There is not enough information to infer relation "__public__.relatedWords.wordsToRelatedWords"

Error: There is not enough information to infer relation "__public__.relatedWords.wordsToRelatedWords"

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!

NotLuksus•11/23/23, 6:34 PM

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

NotLuksus•11/23/23, 6:34 PM

Would simplify your code a lot

NotLuksus•11/23/23, 6:38 PM

//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

NNotLuksus I think you need to change this: ```ts export const wordsToRelatedWordsRelations...

Furki4_4OP•11/23/23, 6:41 PM

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

NNotLuksus ```ts //schema.ts import { relations } from "drizzle-orm"; import { date, integ...

Furki4_4OP•11/23/23, 6:42 PM

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.

Furki4_4OP•11/23/23, 6:44 PM

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

Furki4_4OP•11/23/23, 6:45 PM

btw, thank you for your time and answer creepyheart

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

Similar Threads

Similar Threads

Similar Threads