Drizzle Team•14mo ago

One to Many Self Relations...

I have been trying to establish a self relation in my schema and have not find anything valuable yet. What I want is this: A user will have one of two roles "EDITORS"/"ADMIN". An admin user will have editors associated with them. An Editor user will have only one Admin to them. Also I am using Planetscale :}) My current schema throws this error when i try to run studio Error: There is not enough information to infer relation "users.adminToeditors"

15 Replies

Luxaritas•14mo ago

I think you need to specify the relation name on both sides of the relation?

IzaanOP•14mo ago

I am not sure if I understand that...

IzaanOP•14mo ago

this is the link to my stack overflow question... >> https://stackoverflow.com/questions/77476592/how-do-define-relations-in-planetscalemysql-with-drizzle-orm

Stack Overflow

How do define relations in PlanetScale(MYSQL) with drizzle-orm?

I am trying to learn drizzle-orm, but the thing is I am using Planetscale and man its rough. What I wanna do is to give a user a role either an admin or an editor. Also a user with a role of admin ...

Luxaritas•14mo ago

I'm suggesting you may need to change the admin relation to

admin: one(users, {fields: [users.adminId], references: [users.id], relationName: 'adminToEditors'})

admin: one(users, {fields: [users.adminId], references: [users.id], relationName: 'adminToEditors'})

IzaanOP•14mo ago

Thanks I am not getting the error now, let me see if the functionality is there .... Thanks agian

peterferguson•14mo ago

Hey @Azure D. Cklin did this work? It doesn't seem like it would give the desired functionality I have the same issue https://discord.com/channels/1043890932593987624/1174406045804806164 Hey @Angelelz I seen you have tried https://discord.com/channels/1043890932593987624/1111572543556550738/1148828114345021510 Any idea on one-to-many self relations like this one? in other threads people seem to be just naming the relation but I don't understand how the relation would then know that we are comparing id <-> inReplyToId is it just that there is already a one-to-one self-reference relation for the parent?

Angelelz•14mo ago

This is a good example for you. You can see he has admin and adminToEditors, he was just missing the name on the one side.

peterferguson•14mo ago

so it is the case that the many-to-many self-relation knows what fields to link because we also have the one-to-one self-relation?

Angelelz•14mo ago

I don't think there is a 1-to-1 self relation involved In a many-to-many, you have an intermediary table. And you effectivily have 2 one-to-many relations there

peterferguson•14mo ago

yea sorry I think I confused myself even there basically want I want to be able to do is to query both the parent of an individual message & all the children, grandchildren etc of is with something like this api in the screenshot So I was thinking that this would be a one-to-one a self-relation (for the parent) & a one-to-many relation for the descendants. The types don't seem to line up by just naming the relations

export const messages = sqliteTable(
    'messages',
    {
        // - fields
        id: text('id').notNull(),
        inReplyToId: text('inReplyToId'),
    }
)

export const messagesRelations = relations(messages, ({ one, many }) => ({
    replies: many(messages, { relationName: 'replies' }),
    inReplyTo: one(messages, {
        fields: [messages.inReplyToId],
        references: [messages.xmtpId],
    })
}))

export const messages = sqliteTable(
    'messages',
    {
        // - fields
        id: text('id').notNull(),
        inReplyToId: text('inReplyToId'),
    }
)

export const messagesRelations = relations(messages, ({ one, many }) => ({
    replies: many(messages, { relationName: 'replies' }),
    inReplyTo: one(messages, {
        fields: [messages.inReplyToId],
        references: [messages.xmtpId],
    })
}))

Do you think there is a way to make this work with these relations or should I just break out into a bridge table?

Angelelz•14mo ago

I think you are confused. If you have a message, can that message have several replies (many children)? Can the parent of that message have other children (a parent with many children)? If the answer to both those questions is yes (and they should be), that would be a many-to-many self relation.

peterferguson•14mo ago

yes thank you that makes sense & sorry long day haha

itsyoboieltr•14mo ago

does anyone have an example for a many to many self relation? 😄 I am also looking to implement something similar using the postgres driver: imagine, following users. An User can be followed by many users, and can have many followers themselves.

peterferguson•14mo ago

https://discord.com/channels/1043890932593987624/1111572543556550738/1148828114345021510

IzaanOP•13mo ago

@peterferguson it worked for that particular relation but I have other which I needed to define so, I switched my db to pg. And I am trying my schema in it. If anyone would be willing to help I will post my schema template (drawing), and if you could give me the schema in drizzle that would help a lot.

Gaming

Programming

One to Many Self Relations...