Working with One-to-Many Relations
1. Defining One-to-Many Relation
model User {
id Int @id @default(autoincrement())
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
authorId Int
author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
}| Side | Marker |
|---|---|
| Parent (one) | List field Post[] |
| Child (many) | FK + @relation |
2. Setting Foreign Key Field
| Tip | Detail |
|---|---|
| Index | FK columns auto-indexed for joins |
| Composite FK | Use array in fields/references |
3. Configuring Back Reference
| Side | Field |
|---|---|
| User.posts | Virtual relation, no DB column |
| Post.author | Virtual; FK is authorId |
4. Using Optional Foreign Keys
authorId Int?
author User? @relation(fields: [authorId], references: [id], onDelete: SetNull)| Effect | Detail |
|---|---|
| Detached posts | Posts can exist without an author |
| Cascade choice | Pair optional FK with SetNull |
5. Setting Relation Name
| Use Case | Example |
|---|---|
| Author + reviewer | Two User relations on Post need names |
| Syntax | @relation("AuthoredPosts", fields:[authorId], references:[id]) |
6. Configuring Referential Actions
| Scenario | Recommendation |
|---|---|
| User deleted → posts | Cascade (remove posts) |
| Author optional | SetNull |
| Audit log | Restrict (preserve history) |
7. Querying Related Records
const user = await prisma.user.findUnique({
where: { id: 1 },
include: { posts: { where: { published: true }, take: 10, orderBy: { createdAt: "desc" } } }
});| Option | Detail |
|---|---|
where | Filter included rows |
take/skip | Limit/offset included rows |
orderBy | Sort included rows |
8. Filtering by Related Records
| Operator | Meaning |
|---|---|
some | At least one related row matches |
every | All related rows match |
none | Zero related rows match |
is/isNot | For to-one relations |
await prisma.user.findMany({
where: { posts: { some: { published: true } } }
});9. Counting Related Records
const users = await prisma.user.findMany({
include: { _count: { select: { posts: true } } }
});| Pattern | Detail |
|---|---|
_count | Use inside include or select |
| Filtered count | Pass where: { posts: { where: { published: true } } } |
10. Ordering by Related Fields
await prisma.user.findMany({
orderBy: { posts: { _count: "desc" } }
});| Pattern | Use |
|---|---|
_count | Sort by number of related rows |
| Related field | orderBy: { author: { name: "asc" } } |