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⚡
⚡Z.E.U.S⚡2w 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
JP
JP2w 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⚡
⚡Z.E.U.S⚡2w 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' }),
}))
JP
JP2w 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!
Want results from more Discord servers?
Add your server
More Posts