P
Prisma•2mo 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?
5 Replies
Prisma AI Help
Prisma AI Help•2mo 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•2mo 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•2mo 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•2mo 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?
Nurul
Nurul•3w ago
Apologies for the delay in getting back. You are correct that view preview feature is slated to be Generally Available in the duration of Mar 25 - Aug 25, so we will definetely work on improvements

Did you find this page helpful?