Many-to-Many Self Relation
I'm looking to migrate from Prisma, but I'm having trouble trying to figure out how a many-to-many self relation should be defined.
The prisma schema I has previously was similar to:
Currently my schema looks like:
I'm just not quite sure how to construct the relations properly for the self-referencing with the many-to-many relation.
35 Replies
I was curious about this, so I did some testing.
I got a many to many self relation like this:
Let me now if that helps
Yeah I should have responded to my original post once I had the solution. I had this exact thing, except I did have to remove the
.references(() => ...)
as I was using the PlanetScale DB driver.@Angelelz I'm trying to currently understand this in the context of categories and subcategories. followers and followees is breaking my brain... what would I call the relation names if these were subcategories and categories...?
I've done the following on the intermediate table:
subcategory .... one relationName: 'subcategory'
and
category .... one relationName: 'category'
and category table:
subcategories: many relationName: 'subcategory'
But this gives inverse results (I'd be expecting it to show the subcategories, when it instead shows the parent categories)
Is the relationName the other thing being pointed to?
I feel like for your use case you don't need it as complex as my example
It's a many-to-many self relation
What I wrote was a double self relation
Where a person can have many followers and can be following many others
I might have an example for your case somewhere, let me check
Here you go:
Ah, but can the user have many managers?
Oh... inversely?
Ah no lol
A manager can have many employees
Like a category can have many sub categories
I'm pretty sure I must've completely fried my brain... I was making a many-to-many system
I'll try implementing this, thank you
Does a subcategory need to have many parent categories?
oh crap actually, yeah
I forgot damn it
That's why it was so complex, yeah
They're more like tags
A tag can be listed under several tags, and can list several tags
tags are also one to many?
A category can:
* be listed under several parent categories
* list several subcategories
Ok I guess lol
yeah, it's a bit of a messy system but it works
Well, my initial example will work for this
yes, then my only question would be about the relation names
You gotta name it in a way that makes sense
But it doesn't really matter in terms of implementation
my understand of relation names is like this
it's just that I'm getting the wrong thing when accessing it via the categories table
that's really what i'm wondering about
and my apologies for wasting this much time
Can you put your code here? I'll try to help
Let's see what you got
the problem is when i get the subcategories of a category, I'm really getting the parent categories... is this from a misunderstanding of relation names?
It gets trippy, but you have to think in terms of the table you are writing on
I would first rename category -> parentCategory
Are you missing the other side of the relation in you categoryRelations?
I don't need to know the parent category in my case, only the child one
Ok so I'll update the code to reflect what you said
So what's the point of tracking that relation if you don't need it?
err I added relation names to all of them when I got an error for not having any relation names
i see what you're getting that, i'll adjust the code
oh... so they're opposite
ah....
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaah..........
That should work if you want to keep the many to many self relation
i was thinking relation names referred to themselves
I don't think you need it though
thank you a ton, sorry this took so long
relation names map to the other thing... now i understand
Hello! - @Angelelz I came up with the same solution (just a different naming) when I had to model a many to many self relation, but Im still seeing the "There is not enough information to infer relation" error 😢 do you mind taking a look at my schema and maybe figure out what am I missing?
and the error is the following -> Error: There is not enough information to infer relation "user.followers"
In a many to many relation, you have to have an intermediary table
Your userRelations cannot reference the user table directly
Oh I see now what I was missing x) Thank you so muuuchhh 🙏
Schema
And code
But when i populate the followers, it only return followerId and followeeId, but i want it return value like user table, include name. Plese help me