drizzle-team / drizzle-team/drizzle-orm
[FEATURE]: SELECT DISTINCT ON support in RQB v2
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
### Feature hasn't been suggested before.
- [x] I have verified this feature I'm about to request hasn't been suggested before.
### Describe the enhancement you want to request
Hi!
I couldn't find this feature request anywhere so I decided to open this one.
`SELECT DISTINCT ON` / `SELECT DISTINCT` is a very powerful SQL feature and it's annoying not having it in the new RQB v2.
In my mind it should be a fairly simple implementation seeing that it just needs to add the `DISTINCT` or `DISTINCT ON (...)` after the `SELECT` clause and put the provided columns after that.
The API could follow Prisma's approach and look something like this:
```js
db.query.usersTable.findMany({
distinct: ["role"],
columns: {
id: true,
email: true,
role: true,
},
orderBy: {
role: "asc",
},
});
```
One important detail is that only PostgreSQL supports `DISTINCT ON`.
Because of that, the responsibility of only providing the correct columns for databases that support `DISTINCT ON` could fall on the user for now.
The generated SQL could then differ slightly depending on the database while still keeping the same API shape.
### PostgreSQL
Input:
```js
db.query.usersTable.findMany({
distinct: ["role"],
columns: {
id: true,
email: true,
role: true,
},
orderBy: {
role: "asc",
},
});
```
Output:
```sql
SELECT DISTINCT ON ("role") "id", "email", "role"
FROM "users"
ORDER BY "role" ASC;
```
### Non-PostgreSQL databases
Input:
```js
db.query.usersTable.findMany({
distinct: ["id", "email", "role"],
columns: {
id: true,
email: true,
role: true,
},
});
```
Output:
```sql
SELECT DISTINCT "id", "email", "role"
FROM "users";
```
This would provide a consistent API across databases while still exposing PostgreSQL's more powerful `DISTINCT ON` functionality when available.
Another approach could be having 2 different options, a simple `distinct` and a `distinctOn`, both receiving a list of columns.
Thank you!
Contributor guide
Assessment
This issue has not been assessed yet.