nativeDistinct doesn't work when we include orderBy or take and limit in the queries #22734
Closed as not planned
Description
Bug description
Native DISTINCT doesn't work when we used orderBy
await fastify.prisma.user_info.findMany({
distinct: ["role"],
select: {
role: true
},
orderBy: {
role: {
sort: "desc",
nulls: "last"
}
}
})or use take and limit
await fastify.prisma.user_info.findMany({
distinct: ["role"],
take: req.query.limit,
skip: req.query.page * req.query.limit,
select: {
role: true
}
})The raw queries when used these queries:
SELECT "public"."user_info"."id", "public"."user_info"."role"::text FROM "public"."user_info" WHERE 1=1 ORDER BY "public"."user_info"."id" ASC OFFSET 0It looks like we have to work around this problem by using subqueries: https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by
How to reproduce
.
Expected behavior
No response
Prisma information
I posted them.
Environment & setup
- OS: macOS
- Database: PostgreSQL
- Node.js version: 20
Prisma Version
"prisma": "5.8.1",