Working with Many-to-Many Relations
1. Using Implicit Many-to-Many
model Post {
id Int @id @default(autoincrement())
tags Tag[]
}
model Tag {
id Int @id @default(autoincrement())
posts Post[]
}| Aspect | Detail |
|---|---|
| Join table | Auto-named _PostToTag |
| No extra fields | Can't store metadata on join |
2. Defining Explicit Join Table
model PostTag {
postId Int
tagId Int
post Post @relation(fields:[postId], references:[id], onDelete: Cascade)
tag Tag @relation(fields:[tagId], references:[id], onDelete: Cascade)
@@id([postId, tagId])
}| When | Use |
|---|---|
| Extra fields | e.g., addedAt, addedBy |
| Custom queries | Need direct join-table access |
3. Configuring Join Table Fields
| Field | Purpose |
|---|---|
| Composite PK | @@id([postId, tagId]) |
| Indexes | Add @@index([tagId]) for reverse lookups |
| Actions | Cascade on both FKs |
4. Adding Extra Fields to Join Table
model PostTag {
postId Int
tagId Int
addedAt DateTime @default(now())
addedBy String?
@@id([postId, tagId])
}| Common Fields | Use |
|---|---|
createdAt | Audit timestamp |
position | Ordered membership |
role | Join role (e.g., owner/member) |
5. Querying Many-to-Many Relations
const post = await prisma.post.findUnique({
where: { id: 1 },
include: { tags: true }
});| Pattern | Detail |
|---|---|
| Implicit | Tags inlined directly |
| Explicit | Include nested: postTags: { include: { tag: true } } |
6. Connecting Existing Records
await prisma.post.update({
where: { id: 1 },
data: { tags: { connect: [{ id: 5 }, { id: 7 }] } }
});| Op | Effect |
|---|---|
connect | Add link to existing rows |
set | Replace entire set |
7. Disconnecting Related Records
| Op | Effect |
|---|---|
disconnect | Remove individual link |
set: [] | Remove all links |
| Explicit join | Use delete on PostTag rows |
8. Setting Multiple Relations
await prisma.post.update({
where: { id: 1 },
data: { tags: { set: [{ id: 1 }, { id: 2 }, { id: 3 }] } }
});| Note | Detail |
|---|---|
| set | Idempotent; current = provided list |
| Mix | Combine connect+disconnect in one update |
9. Filtering by Related Records
await prisma.post.findMany({
where: { tags: { some: { name: { in: ["typescript", "react"] } } } }
});| Filter | Use |
|---|---|
some | At least one tag matches |
every | All tags satisfy condition |
none | No matching tags |
10. Managing Relation Ordering
| Approach | Detail |
|---|---|
| Implicit | No inherent order; sort included list via orderBy |
| Explicit | Add position Int in join table |
| Stable ordering | Combine position + createdAt |