P
Prisma•4w ago
ptrxyz

n:m relations between tables and view

I have a view "DepartmentInfo" as well as model "User". The user can be part of many departments and a department can have many users -- we are talking about n:m relation. I defined the view as well as the model in my schema, but prisma doesn't seem to create the relation tables (_User_DepartmentInfo) for that relation. As soon as I change the view to a model, the table is generated. What am I doing wrong here?
4 Replies
Prisma AI Help
Prisma AI Help•4w ago
You selected to wait for the human sages. They'll share their wisdom soon. Grab some tea while you wait, or check out #ask-ai if you'd like a quick chat with the bot anyway!
ptrxyz
ptrxyzOP•4w ago
to add context: 
// this schema adds relation tables
model User {
    id Int @id
    departments Department[]
}

model Department {
    id Int @id
    users User[]
}
// this schema adds relation tables
model User {
    id Int @id
    departments Department[]
}

model Department {
    id Int @id
    users User[]
}
// this schema DOES NOT add relation tables
model User {
    id Int @id
    departments Department[]
}

view Department {
    id Int @id
    users User[]
}
// this schema DOES NOT add relation tables
model User {
    id Int @id
    departments Department[]
}

view Department {
    id Int @id
    users User[]
}
I don't get what the problem is, can someone shed some light? Does anyone know more about this?
Nurul
Nurul•4w ago
I think this is the intended behaviour because Prisma doesn't create or manage relation tables for views. Prisma does not create or run any SQL related to views. For many-to-many relationships, Prisma needs to create a relation table (like _User_DepartmentInfo in your case), but since it doesn't generate SQL for views, this relation table isn't being created. Did you consider creating an explicit many-to-many relation by defining your own join model?
ptrxyz
ptrxyzOP•3w ago
I figured that when i create the tables manually with the names prisma expects, then i can use them in the 'normal' way: prisma.myview.findMany({ include: { ... }}) However i then it is a bit cumbersome to maintain the tables: 
model FooToBar {
 A Int
 B Int
 @@ignore
 @@map(....)
 @@index([ ... ])
} 
model FooToBar {
 A Int
 B Int
 @@ignore
 @@map(....)
 @@index([ ... ])
}
This would probably be fine for a single relation, but it doesn't scale well. :/ Further it seems that prisma doesn't check the validity of column types. I could use "String" instead of "Int" for example and I wouldn't get any "compile time" errors, only failures during runtime :/ Do you know if there will be added a bit more love to views in the future? I found that the view preview feature is on the agenda for March/25 -> August/25. So there might still be improvements?

Did you find this page helpful?