one-to-many self reference

Hello guys, this is a simplified section of my schema:

 typescript
export const contacts = mysqlTable("contacts", {
  id: int("id").primaryKey(),
  parentId: int("parent_id"),
  ...
}, (table) => {
  return {
    parentReference: foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "fk_contacts_parent_id"
    }),
  };
})

 typescript
export const contacts = mysqlTable("contacts", {
  id: int("id").primaryKey(),
  parentId: int("parent_id"),
  ...
}, (table) => {
  return {
    parentReference: foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "fk_contacts_parent_id"
    }),
  };
})

contacts are families or family members, where parent_id points to the family I tried to update the above code:

  parentId: int('parent_id').references(() => contacts.id),

  parentId: int('parent_id').references(() => contacts.id),

but I'm getting this error:

'contacts' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer.ts(7022)

'contacts' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer.ts(7022)

and also have this:

export const contactsRelations = relations(contacts, ({ many, one }) => ({
  contactCustomFields: many(contactCustomFields),
  parent: one(contacts, {
    fields: [contacts.parentId],
    references: [contacts.id],
  }),
  familyMembers: many(contacts),
})),

export const contactsRelations = relations(contacts, ({ many, one }) => ({
  contactCustomFields: many(contactCustomFields),
  parent: one(contacts, {
    fields: [contacts.parentId],
    references: [contacts.id],
  }),
  familyMembers: many(contacts),
})),

even without the .references, the below code silently fails:

const result = await db.query.contacts.findMany({
    with: {
      parent: true,
      familyMembers: true,
    },
    limit: 1
  })

const result = await db.query.contacts.findMany({
    with: {
      parent: true,
      familyMembers: true,
    },
    limit: 1
  })

4 Replies

⚡Z.E.U.S⚡•7mo ago

@JP Hey! You can write this, but it's the same as the first code section above:

parentId: integer("parent_id").references((): AnyPgColumn => contacts.id)

parentId: integer("parent_id").references((): AnyPgColumn => contacts.id)

https://arc.net/l/quote/wofibpcw When you have multiple relations between the same two tables you need to specify relation_name for them https://orm.drizzle.team/docs/rqb#disambiguating-relations

JPOP•7mo ago

Thank you @⚡Z.E.U.S⚡! this worked:

export const contacts = mysqlTable("contacts", {
  id: int("id").primaryKey(),
  parentId: int("parent_id").references((): AnyMySqlColumn => contacts.parentId),
  ...
}, (table) => {
  return {
    parentReference: foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "fk_contacts_parent_id"
    }),
  }
})

export const contacts = mysqlTable("contacts", {
  id: int("id").primaryKey(),
  parentId: int("parent_id").references((): AnyMySqlColumn => contacts.parentId),
  ...
}, (table) => {
  return {
    parentReference: foreignKey({
      columns: [table.parentId],
      foreignColumns: [table.id],
      name: "fk_contacts_parent_id"
    }),
  }
})

I'm not sure if it's right, but it doesn't show errors, so, nice!! Also this code does not show any error:

export const contactsRelations = relations(contacts, ({ many, one }) => ({
  contactCustomFields: many(contactCustomFields, { relationName: 'contactCustomFields' }),
  familyMembers: many(contacts, { relationName: 'familyMembers' }),
}))

export const contactsRelations = relations(contacts, ({ many, one }) => ({
  contactCustomFields: many(contactCustomFields, { relationName: 'contactCustomFields' }),
  familyMembers: many(contacts, { relationName: 'familyMembers' }),
}))

but this code below fails with There is not enough information to infer relation "contacts.familyMembers

const result = await db.query.contacts.findMany({
      with: {
        familyMembers: true,
      },
      limit: 1
    })

const result = await db.query.contacts.findMany({
      with: {
        familyMembers: true,
      },
      limit: 1
    })

Angelelz
•
166d ago
I think your problem is that you have a relationName defined only on one side.
You have to either delete it or add it on both sides

Angelelz
•
166d ago
I think your problem is that you have a relationName defined only on one side.
You have to either delete it or add it on both sides

I saw this, I'll try to fix it

⚡Z.E.U.S⚡•7mo ago

You need to specify one relation too:

export const contactsRelations = relations(contacts, ({ many, one }) => ({
  contactCustomFields: many(contactCustomFields, { relationName: 'contactCustomFields' }),
parent: one(contacts, {
    fields: [contacts.parentId],
    references: [contacts.id],
    relationName: 'family'
  }),
  familyMembers: many(contacts, { relationName: 'family' }),
}))

export const contactsRelations = relations(contacts, ({ many, one }) => ({
  contactCustomFields: many(contactCustomFields, { relationName: 'contactCustomFields' }),
parent: one(contacts, {
    fields: [contacts.parentId],
    references: [contacts.id],
    relationName: 'family'
  }),
  familyMembers: many(contacts, { relationName: 'family' }),
}))

JPOP•7mo ago

but I don't know in this case which is the 'other side' of the relationship 🙂 I'll try that right now! Thank you @⚡Z.E.U.S⚡! it's working. I've been struggling with this for hours!

Gaming

Programming

one-to-many self reference