Sorting and Ordering Results
1. Ordering by Single Field
await prisma.post.findMany({ orderBy: { createdAt: "desc" } });
| Direction | Value |
|---|
| Ascending | "asc" |
| Descending | "desc" |
2. Ordering by Multiple Fields
await prisma.post.findMany({
orderBy: [{ published: "desc" }, { createdAt: "desc" }]
});
| Aspect | Detail |
|---|
| Priority | Array order = SQL ORDER BY priority |
| Mixed direction | Each entry independent |
3. Using Ascending Order
| Use | Detail |
|---|
| Default | If orderBy omitted (DB-dependent) |
| Alphabetical names | { name: "asc" } |
4. Using Descending Order
| Common Use | Detail |
|---|
| Recent first | createdAt: "desc" |
| Highest score | score: "desc" |
await prisma.post.findMany({ orderBy: { author: { name: "asc" } } });
| Aspect | Detail |
|---|
| To-one | Sort by joined column |
| To-many | Only _count supported |
6. Using Null Positioning
await prisma.user.findMany({
orderBy: { lastLogin: { sort: "desc", nulls: "last" } }
});
| Option | Effect |
|---|
nulls: "first" | Nulls before non-null |
nulls: "last" | Nulls after non-null |
| Support | PG, SQLite (with workaround on MySQL) |
7. Combining Sort with Filters
| Pattern | Detail |
|---|
| where + orderBy | Apply filter, then sort |
| Index | Match indexed columns for performance |
8. Ordering by Aggregations
await prisma.user.findMany({
orderBy: { posts: { _count: "desc" } },
take: 10
});
| Aggregator | Use |
|---|
_count | By related row count |
_sum / _avg | Via groupBy+orderBy |
9. Using Case-Insensitive Sorting
| DB | Approach |
|---|
| PG | Use LOWER(field) via raw query |
| MySQL | Use ci collation |
| App | Sort post-query for small sets |
10. Implementing Relevance Sorting
await prisma.post.findMany({
where: { title: { search: "react & typescript" } },
orderBy: { _relevance: { fields: ["title"], search: "react", sort: "desc" } }
});
| Aspect | Detail |
|---|
| Preview | fullTextSearchPostgres or MySQL fulltext |
| Use | Rank-based ordering for search |